Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004

    Unanswered: Beginner question on Altering constraints in Tables..

    Hello all,

    Just started learning SQL recently.

    But one thing i'm still not clear on is alter tables relationships after they've been created.

    Instead of creating a foreign key when the table is first created - i create the table and then run a query to set the foreign key and relationship (one-to-one, one-to-many etc)

    Anyways, long story short is i want to create a one-to-one relationship with a table but am having problems with adding more than one constraint at a time when altering a table.

    Understand yet? Easiest thing to do is show you:

    I have 2 tables: Branch_Table and Employee_Table

    I want to create a one-to-one relationship between emp_id on the Branch_Table and manager_id on the Employee_Table.

    The SQL i've written which doesn't seem to work is:

    ALTER TABLE Branch_Table
    Add Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)
    Add Constraint Branch_Table_UQ1 Unique (manager_id)
    References Employee_Table (emp_id));

    Am having trouble with that second Add constraint (UQ1 unique). I know it's something to do with the Add syntax above.

    So basically, my question is can i create a one-to-one relationship with just the one SQL Query? And how would i do it?

    Many thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    This syntax may vary between DBMSs, but this works in Oracle:
    ALTER TABLE Branch_Table
    Add (Constraint Branch_Table_UQ1 Unique (manager_id),
         Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)
             References Employee_Table (emp_id));
    (You had the REFERENCES clause on the wrong constraint, by the way).

Posting Permissions

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