Rock Rock - 1 year ago 52
SQL Question

Oracle Sql Check Constraint != other table

I want to include a check constraint on a table between the primary key of the latter and the primary key of another table and insert! = Between the two, how can i do it?
I've been tempted like this:

CREATE TABLE RESPONSABILE(
ID_RESP CHAR(10) REFERENCES UTENTE(ID_USER) PRIMARY KEY,
CODICE_FISCALE VARCHAR(16) NOT NULL UNIQUE,
NOME VARCHAR(15) NOT NULL,
COGNOME VARCHAR(15) NOT NULL,
CONSTRAINT CK_FI CHECK (REGEXP_LIKE(CODICE_FISCALE,'^[A-Z]{6}[\d+]{2}[ABCDEHLMPRST]{1}[\d+]{2}([A-Z]{1}[\d+]{3})[A-Z]{1}$','I')),
CONSTRAINT CK_RE CHECK (ID_RESP != (ID_PR) REFERENCES PR(ID_PR) AND ID_RESP != (ID_CLIENTE) REFERENCES CLIENTE(ID_CLIENTE))
);


But I have not had any positive results

Answer Source

It appears from the text of your syntactically incorrect check constraint, that you want to enforce a multi-table check constraint. The only way to do this in Oracle (and maybe any RDBMS) is with a trigger. You cannot reference multiple tables in a check constraint.

However, depending upon your Oracle version, and according to the Oracle constraint documentation, you might be able to define a rather complex foreign key constraint as this implies:

You cannot define a foreign key constraint in a CREATE TABLE statement that contains an AS subquery clause. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download