Oracle Sql Check Constraint！=其他表 [英] 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

推荐答案

从语法上不正确的检查约束的文本来看，您似乎想强制执行多表检查约束.在Oracle(可能还有任何RDBMS)中执行此操作的唯一方法是使用触发器.您不能在检查约束中引用多个表.

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.

但是，取决于您的Oracle版本，并根据

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:

您不能在包含AS子查询子句的CREATE TABLE语句中定义外键约束.相反，您必须创建没有约束的表，然后稍后使用ALTER TABLE语句添加表

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

这篇关于Oracle Sql Check Constraint！=其他表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！