1

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.

2
  • 1
    Which specific database are you using? Microsoft SQL Server?
    – joanolo
    Apr 24 '17 at 7:00
  • I am using SQL Server 2014
    – Fjatwood
    Apr 26 '17 at 1:50
1

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
);
1
  • Thank you. I have learned a lot with this experience but am still in the learning process.
    – Fjatwood
    Apr 26 '17 at 1:41

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .