How to set up primary and foreign keys referencing two different tables

foreign keyprimary-keyreferential-integrity

I need to have two primary keys also to be the foreign keys in the same table. These two keys are referenced in different tables. I keep getting error messages that are unclear to me. My code is as follows:

CREATE TABLE ASSIGNMENT (
        ProjectID       INT             NOT NULL,
        EmployeeNumber  INT             NOT NULL,
        HoursWorked     NUMERIC(6,2)    NULL,
CONSTRAINT  AssignmentPK            PRIMARY KEY(ProjectID,EmployeeNumber),

CONSTRAINT  AssignmentFK            FOREIGN KEY(EmployeeNumber, ProjectID)
        REFERENCES  EMPLOYEE(EmployeeNumber)
            ON UPDATE NO ACTION
            ON DELETE NO ACTION,

    REFERENCES  PROJECT(ProjectID)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

The current error message is:

Msg 142, Level 15, State 2, Line 2
Incorrect syntax for definition of the 'TABLE' constraint.

Please advise.

Best Answer

The actual error message you are getting is because your CREATE TABLE statement is malformed as you cannot have a foreign key refer to more than one table. However you can still have the structure that you require by having two separate foreign keys. You can only use a composite foreign key if all columns that will be added to it belong to the same key in the target table.

CREATE TABLE ASSIGNMENT (
    ProjectID       INT             NOT NULL,
    EmployeeNumber  INT             NOT NULL,
    HoursWorked     NUMERIC(6,2)    NULL,

CONSTRAINT AssignmentPK PRIMARY KEY (ProjectID, EmployeeNumber),
CONSTRAINT AssignmentFK_Project FOREIGN KEY (ProjectID)
  REFERENCES PROJECT(ProjectID)
   ON UPDATE NO ACTION
   ON DELETE CASCADE,
CONSTRAINT AssignmentFK_Employee FOREIGN KEY (EmployeeNumber)
  REFERENCES EMPLOYEE(EmployeeNumber)
   ON UPDATE NO ACTION
   ON DELETE NO ACTION
);

Related Solutions

Oracle 11g – Creating Composite Primary Key from Multiple Tables

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

SQL Server – How to Cascade Primary Key Update to All Referencing Foreign Keys

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint.

If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update.

EDIT: Since you do not have the ON UPDATE CASCADE constraint, but you want to have that set up, it is a bit of work. SQL Server does not support altering the constraints to a new setting.

It is necessary to iterate through each table that has a FK constraint to the PK table. For each table with the FK:

ALTER TABLE to drop the existing FK constraint.
ALTER TABLE again to create the ON UPDATE CASCADE constraint for the FK in question.

This takes a bit of effort, but would result in your constraint being properly set for your case.

EDIT 2: The information that you need is found in sys.foreign_keys. You can select from that table to get all the information you need.

A post from John Paul Cook can be found here:

(http://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx)

This code will drop and create ALL FK constraints in a database. You should be able to work from that to make only the changes that you want in your database.

Best Answer

Related Solutions

Oracle 11g – Creating Composite Primary Key from Multiple Tables

SQL Server – How to Cascade Primary Key Update to All Referencing Foreign Keys

Related Question