I am trying to create the Employee table and disable the foreign key constraint (the dept table is not created yet!). But it didn't work, did I use the DISABLE in the last line correctly? Please advise.
Create TABLE employee
(fname VARCHAR2(15) NOT NULL,
lname VARCHAR2(15) NOT NULL,
ssn VARCHAR2(9) NOT NULL,
salary NUMBER (10,2),
dno NUMBER(1) NOT NULL,
CONSTRAINT emp_ssn_pk PRIMARY KEY (SSN),
CONSTRAINT emp_superssn_fk FOREIGN KEY(superssn) REFERENCES dept(mgrssn) disable);
I don't believe that you can do that. I'm not sure about your syntax, but the problem is that Oracle *still* needs to create the constraint. This means an entry into a system table that also contains IDs for both the table and the column that the constraint points to. This table and column have to actually exist in order for this to happen. Creating a disabled constraint simply saves you from having it run right away. This is like creating a procedure that you don't plan on executing for a while. The objects that the procedure references must still exist at the time you create the procedure. You will need to create the table without the constraint, then add it back in (disabled if you like) after you have created the DEPT table.