主页 / dba / 问题 / 189296
Accepted
Mio
Asked: 2017-10-26 08:18:01 +0800 CST

添加数组表约束，如果其他列为 false，则为空或 null

  • 772

我在现有表上添加约束jobs：

DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
CREATE TABLE jobs(
status job_state NOT NULL DEFAULT 'UNSTARTED',
error_message text[]
);

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false 
   CHECK ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED') AND ( error_message IS NULL OR error_message = '{}' ));

我想要的是

INSERT INTO jobs VALUES ('UNSTARTED', null);
INSERT INTO jobs VALUES ('STARTED', null);
INSERT INTO jobs VALUES ('FAILED', null); -- I would like to be able to do this

我知道这error_message != '{}' OR error_message IS NOT NULL写得不正确。但是我一直在寻找一种方法来编写它。

postgresql constraint

2 个回答

  1. Best Answer

    应该使用OR类似的方式编写约束(NOT A) OR B

    ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false 
   CHECK ( NOT ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED')) OR ( error_message IS NULL OR error_message = '{}' ));

INSERT INTO jobs VALUES ('UNSTARTED', null); -- no error
INSERT INTO jobs VALUES ('STARTED', null); -- no error
INSERT INTO jobs VALUES ('FAILED', null); -- no error
INSERT INTO jobs VALUES ('COMPLETED', null); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('STARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('FAILED', '{}'); -- no error
INSERT INTO jobs VALUES ('COMPLETED', '{}'); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('COMPLETED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('STARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('FAILED', '{"error message"}'); -- no error
    • 1

  2. 你为什么这么做

    CHECK ( NOT (failed IS FALSE AND (error_message != '{}' OR error_message IS NOT NULL)) );

    并不是

    CHECK (
  failed IS FALSE
  AND (error_message = '{}' OR error_message IS NULL )
);

    failed或者，最好不要像error_message使用单个 ENUM那样跨多个布尔值保持状态。

    CREATE TYPE terminal_job_state AS ENUM ( 'FAILED', 'COMPLETED' )
    • -1

相关问题