SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL

  1. #1
    Join Date
    Jul 2000
    Posts
    7

    Question

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Oct 2000
    Posts
    123
    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

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width