How to use one table attribute or its value as a constraint on another table attribute
I am creating a database for a housing management system. I have the following two tables.
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'))) );
I want to apply Constraint to a table attribute HOUSE
which is -> plotiID (foreign key) and Constraint should not allow the user to enter any table plotID
in HOUSE
if its status is "UNSOLD" in the table PLOT
.
I've tried this. but it doesn't work:
CONSTRAINT PLOT_CONS CHECK (plotID (status IN('SOLD')));
source to share
And a BEFORE INSERT
trigger is the most appropriate way to deal with it. You can use it to check all the conditions you need before the insert actually happens. Something like the following will help you.
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;
/
More information on Oracle triggers: https://docs.oracle.com/cloud/latest/db112/LNPLS/triggers.htm
source to share