SQL Database code:
CREATE TABLE MYBUILDING ( B_NUMBER NUMBER(1) NOT NULL, NAME VARCHAR2(25) NOT NULL, ADDRESS VARCHAR2(40) NOT NULL, P_CODE VARCHAR2(6) , FLOOR_NUM_UP NUMBER(3) DEFAULT '1' NOT NULL CHECK (FLOOR_NUM_UP >= 0), FLOOR_NUM_DOWN NUMBER(3) DEFAULT '0' NOT NULL CHECK (FLOOR_NUM_DOWN >= 0), CONS_DATE DATE NOT NULL, PRIMARY KEY (B_NUMBER) ); ------- CREATE TABLE FLOOR ( B_NUMBER NUMBER(1) NOT NULL, F_NUMBER NUMBER(2) NOT NULL CHECK (F_NUMBER >= -4 AND F_NUMBER <= 25), SPACE_M NUMBER(4) NOT NULL CHECK (SPACE_M > 0), PRIMARY KEY (B_NUMBER, F_NUMBER), FOREIGN KEY (B_NUMBER) REFERENCES MYBUILDING ); ------ CREATE TABLE TENANT ( TENANT_NUM NUMBER(3) NOT NULL, TENANT_NAME VARCHAR2(40) NOT NULL, C_NAME VARCHAR2(40) NOT NULL, C_ADDRESS VARCHAR2(40), P_CODE VARCHAR2(6), P_NUMBER NUMBER(12), PRIMARY KEY (TENANT_NUM) ); ------- CREATE TABLE LOCATION ( B_NUMBER NUMBER(1) NOT NULL, F_NUMBER NUMBER(2) NOT NULL, L_NUMBER NUMBER(3) NOT NULL CHECK (L_NUMBER > 0 AND L_NUMBER < 100 ), SPACE_M NUMBER(4) NOT NULL, RATE NUMBER(5) NOT NULL CHECK (RATE >= 0 AND RATE <= 350), RENT NUMBER(4) NOT NULL, S_DATE DATE, E_DATE DATE, TENANT_NUM NUMBER(3), PRIMARY KEY (L_NUMBER, F_NUMBER, TENANT_NUM), CONSTRAINT FK_TENANT FOREIGN KEY (TENANT_NUM) REFERENCES TENANT ON DELETE SET NULL, FOREIGN KEY (B_NUMBER, F_NUMBER) REFERENCES FLOOR ); Hello Everyone! I made a series of tables and i think i did everything correct. i entered my input values on all the tables without a hitch except for the LOCATION table.
this is the Error thats giving me.
INSERT INTO LOCATION VALUES (1, 1, 1, 300, 101, 0, '01-JAN-2004', '30-JUN-2004', 1); INSERT INTO LOCATION VALUES (1, 1, 1, 300, 101, 0, '01-JAN-2004', '30-JUN-2004', 1) ERROR at line 1: ORA-02291: integrity constraint (ASSIGNMENT.FK_TENANT) violated - parent key not found.
Any help and tips will be appreciated. THanks!
fixed.
CREATE TABLE LOCATION( B_NUMBER NUMBER(1) NOT NULL, F_NUMBER NUMBER(2) NOT NULL, L_NUMBER NUMBER(3) NOT NULL CHECK (L_NUMBER > 0 AND L_NUMBER < 100 ), SPACE_M NUMBER(4) NOT NULL, RATE NUMBER(5) NOT NULL CHECK (RATE >= 0 AND RATE <= 350), RENT NUMBER(4) NOT NULL, S_DATE DATE, E_DATE DATE, TENANT_NUM NUMBER(3) NULL, PRIMARY KEY (L_NUMBER, B_NUMBER, F_NUMBER, TENANT_NUM), FOREIGN KEY (TENANT_NUM) REFERENCES TENANT ON DELETE SET NULL, FOREIGN KEY (B_NUMBER, F_NUMBER) REFERENCES FLOOR ); 1 Answer
Your insert statement is trying to insert a value into column TENANT_NUM which doesn't exist in the TENANT table - (There's a foreign key constraint on the Location table).
2
