Oracle-CHECK约束-仅允许在不同列中的值不为null时更改列

Question

所以我有下表：

CREATE TABLE Projects (
ID  INTEGER  CONSTRAINT ProjPK PRIMARY KEY, 
Column1 VARCHAR2(30) NOT NULL ,
Column2 VARCHAR2(10) NOT NULL ,
Column3 INTEGER NULL ,
Column4 VARCHAR2(20) NULL ,
Column5 INTEGER NOT NULL ,
Column6 INTEGER NULL ,
Column7 DATE NULL
);

我需要为Column7设计一个CHECK约束，它只允许在第4列和第6列不为NULL时将Column7更改为不同的值。

我想表达的是，如果第4列或第6列为NULL，则第7列必须为NULL，只有当第4列和第6列不为NULL时，才能分别为NOT NULL。我希望这是有道理的。如有任何帮助，我们将不胜感激。

Answer 1

使用一个简单的检查约束来定义您的限制

alter table projects add CONSTRAINT check_null 
CHECK ( (column4 is  null and column6 is null and column7 is null) or 
(column4 is not null and column6 is not null and column7 is not null) );

某些测试

失败，因为第7列已定义，第4、6列未

INSERT INTO projects (id, column1, column7)  VALUES (1, 'fail', SYSDATE);
--ORA-02290: check constraint (xxx.CHECK_NULL) violated

OK，因为所有列都是NULL

INSERT INTO projects (id, column1, column7)  VALUES (1, 'OK', null);
-- 1 row inserted. 

或全部定义。

INSERT INTO projects (id, column1,column4, column6, column7)  VALUES (2, 'OK','x',1, SYSDATE);
-- 1 row inserted. 

注意，如果简单的CHECK约束就足够了，则永远不要使用触发器。

Answer 2

我认为这是触发器的任务。

简化表格：

SQL> CREATE TABLE projects
2  (
3     id        INTEGER CONSTRAINT projpk PRIMARY KEY,
4     column1   VARCHAR2 (30),
5     column2   VARCHAR2 (10),
6     column3   INTEGER,
7     column4   VARCHAR2 (20),
8     column5   INTEGER,
9     column6   INTEGER,
10     column7   DATE
11  );
Table created.

触发器：

SQL> CREATE OR REPLACE TRIGGER trg_ch7
2     BEFORE UPDATE
3     ON projects
4     FOR EACH ROW
5  BEGIN
6     IF :old.column7 <> :new.column7
7     THEN
8        IF     :new.column4 IS NULL
9           AND :new.column6 IS NULL
10        THEN
11           NULL;
12        ELSE
13           raise_application_error (
14              -20000,
15              'Col 7 can not be changed because cols 4 and 6 are not empty');
16        END IF;
17     END IF;
18  END;
19  /
Trigger created.
SQL>

测试：

SQL> INSERT INTO projects (id, column1, column7)
2       VALUES (1, 'test', SYSDATE);
1 row created.
SQL>
SQL> UPDATE projects
2     SET column7 = SYSDATE + 1
3   WHERE id = 1;
1 row updated.
SQL>
SQL> UPDATE projects
2     SET column4 = 'not empty'
3   WHERE id = 1;
1 row updated.
SQL>
SQL> UPDATE projects
2     SET column7 = SYSDATE + 2
3   WHERE id = 1;
UPDATE projects
*
ERROR at line 1:
ORA-20000: Col 7 can not be changed because cols
4 and 6 are not empty
ORA-06512: at "SCOTT.TRG_CH7", line 9
ORA-04088: error during execution of trigger
'SCOTT.TRG_CH7'

SQL>