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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.