CHECK约束值

Question

我有一个非常简单的一块一直现在窃听我的大脑几个小时：

CREATE TABLE driving_school_questions(

    question VARCHAR2(200), 
    picture VARCHAR2(2), 
    CONSTRAINT q_p_chk CHECK ((question LIKE '%see picture%' AND picture IS NOT NULL) 
                AND 
           (question LIKE '% %' OR picture IS NULL)) 

); 

我试图在这里实现的是创造一个约束如果问题字段包含“see picture”，那么图片不能为NULL，否则对于每个不包含“see picture”的问题都可以为NULL。我已经在CHECK子句中尝试过其他表达式，但是已经有效。

这些插入精细的工作：

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blahblah see picture', '23'); --NOT NULL so ok for now 

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blah blah see picture ', NULL); --It's ok to be NULL(constraint violated) 

这不是工作：

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blah blah', NULL);--it should work but constraint violation 

Answer 1

我想你只需要一个单一的检查，防止“见图片”和空单组合：

CREATE TABLE driving_school_questions(
    question VARCHAR2(200), 
    picture VARCHAR2(2), 
    CONSTRAINT q_p_chk CHECK (NOT(question LIKE '%see picture%' AND picture IS NULL)) 
); 

这看起来对于单一组合，并检查是不是你有什么。

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blahblah see picture', '23'); 

1 row inserted. 

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blah blah see picture ', NULL); 

Error report - 
ORA-02290: check constraint (SCHEMA.Q_P_CHK) violated 

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blah blah', NULL); 

1 row inserted. 

由于@vkp建议，你可以使用正则表达式来检查“见图片”的一部分，以避免错误匹配，并且也是区分问题：

CONSTRAINT q_p_chk CHECK (NOT(REGEXP_LIKE(question, '(^|\s)see picture(\s|$)', 'i') 
     AND picture IS NULL)) 

这意味着双方的这些都OK太：

INSERT INTO driving_school_questions (question, picture) 
VALUES ('blahblah isee pictures', null); 

INSERT INTO driving_school_questions (question, picture) 
VALUES ('See picture', '25'); 

，但这是不允许的：

INSERT INTO driving_school_questions (question, picture) 
VALUES ('See Picture', null); 

您甚至可能只想将整个字符串值限制为'see picture'或几个可能的值之一;你也可以用稍微修改的正则表达式来完成。