唯一键 [定制]

debugcn 发表于 Dev

TG_javacoder

我有一张表来存储地址。有一个字段来标识当前或活动地址。一名员工可以有多个地址，但只能有一个活动地址。address_status 字段只有两种状态，即存在和旧。它们用 ID 1 表示活动，2 表示旧。因此，在表中，一名员工只能包含一个活动地址或 address_status = 1 但多个 address_status = 2。

请建议如何解决上述情况。

沃恩弗里德·多姆沙伊特

你可以这样做：

CREATE TABLE EMPLOYEE (
    address VARCHAR2(100) NOT NULL,
    address_status INTEGER,
    address_status_text VARCHAR2(20) GENERATED ALWAYS AS (CASE address_status WHEN 1 THEN 'active' ELSE 'old' END)
    );

ALTER TABLE EMPLOYEE ADD CONSTRAINT ADDRESS_STATUS CHECK (ADDRESS_STATUS IS NULL OR ADDRESS_STATUS = 1);
ALTER TABLE EMPLOYEE ADD CONSTRAINT ADDRESS_ACTIVE UNIQUE (ADDRESS_STATUS);


INSERT INTO EMPLOYEE (address, address_status) VALUES ('New York', 1);
INSERT INTO EMPLOYEE (address, address_status) VALUES ('Houston', NULL);
INSERT INTO EMPLOYEE (address, address_status) VALUES ('Houston', NULL);
INSERT INTO EMPLOYEE (address, address_status) VALUES ('Los Angeles', NULL);

SELECT *
FROM EMPLOYEE;


+----------------------------------------------+
|ADDRESS    |ADDRESS_STATUS|ADDRESS_STATUS_TEXT|
+----------------------------------------------+
|Houston    |              |old                |
|Houston    |              |old                |
|Los Angeles|              |old                |
|New York   |1             |active             |
+----------------------------------------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。