Rock Rock - 5 months ago 26
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