sql - 如何使用一个表属性或其值作为对另一个表属性的约束

Question

我正在为住房社会管理系统制作数据库。我有以下两个表。

CREATE TABLE PLOT ( plotID number(3) PRIMARY KEY,
                    blockName char REFERENCES blockINFO, 
                    ownerID number(13) REFERENCES OWNER,
                    status varchar2(10) NOT NULL, 
         CONSTRAINT PLOT_CONS CHECK ((Status IN ('SOLD', 'UNSOLD'))) );

CREATE TABLE HOUSE (houseID number(3) PRIMARY KEY, 
                    plotID number(3)  REFERENCES PLOT )
         CONSTRAINT PLOT_CONS CHECK (plotID (Status NOT IN ( 'UNSOLD'))) );

我想对 HOUSE 表的属性应用约束 --> plotiID(外键)& 约束不应允许用户在 中输入任何 plotID >HOUSE 表，如果它在 PLOT 表中的status 是'UNSOLD'。

我试过了。但它不起作用:

CONSTRAINT PLOT_CONS CHECK (plotID (status IN('SOLD')));

Answer 1

BEFORE INSERT 触发器是处理此问题的最合适方法。您可以使用它来检查插入实际发生之前所需的所有条件。类似以下的内容会对您有所帮助。

CREATE OR REPLACE TRIGGER checkPlotStatus
BEFORE INSERT 
    ON HOUSE
DECLARE
    plot_status VARCHAR(10);
BEGIN
    --Get the plot status for the plot id you are trying to insert
    SELECT status INTO plot_status
    FROM PLOT
    WHERE plotID=:new.plotID;

    --If that status is UNSOLD, then raise an application error.
    --Else, do the insertion
    IF plot_status = 'UNSOLD' THEN
         RAISE_APPLICATION_ERROR(-20000, 'Status of plot is unsold!');
    ELSE
         INSERT INTO HOUSE
         VALUES (:new.houseID, :new.plotID);
    ENDIF;
END;
/

有关 Oracle 触发器的更多信息:https://docs.oracle.com/cloud/latest/db112/LNPLS/triggers.htm