SQL

[Ray]

Hey all,

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,
minit VARCHAR2(2),
lname VARCHAR2(15) NOT NULL,
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(30),
sex VARCHAR2(1),
salary NUMBER (10,2),
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL,
CONSTRAINT emp_ssn_pk PRIMARY KEY (SSN),
CONSTRAINT emp_superssn_fk FOREIGN KEY(superssn) REFERENCES dept(mgrssn) disable);


Thanks
RAY

[chrisrlong]

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.

- Chris

[mber]

Just for hint:

U can use the deferrable option ---- define the RI is deferrable.

"constraint t_constraint foreign key(col.....,col...)
references table_name deferrable".

Then in program or trigger U can turn off/on this reference by:

"set constraint t_contraint deferred/immediate".

Take care

[akkerend]

You cannot reference a table that does not exist, even if you disable the constraint or set if deferred.

Create the table employee without foreign key constaint, like chrisrlong suggested. With ALTER TABLE command you can add the constraint after you create the dept table.