-1

I am currently I am working in sql server and have the following code. I am trying to create two foreign keys under the 'ASSIGNMENT' table, as well as create a primary key for the 'ASSIGNMENT' table. However, I keep running into problems when trying to execute my codes for creating the foreign keys and when trying to create the primary key.

For the foreign keys, my code is: ALTER TABLE dbo.ASSIGNMENT ALTER COLUMN ProjectID INTEGER; ADD CONSTRAINT PROJECT_FK FOREIGN KEY (ProjectID) REFERENCES dbo. PROJECT (ProjectID);

ALTER TABLE dbo.ASSIGNMENT ALTER COLUMN EmployeeNumber CHAR(25);
ADD CONSTRAINT EMPLOYEE_FK FOREIGN KEY (EmployeeNumber) REFERENCES 
dbo.EMPLOYEE (EmployeeNumber);

The message that pops up is: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'CONSTRAINT'. Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'CONSTRAINT'.

My instructor wants us to use ALTER table in order to create the foreign keys, but I am unsure how to create them for the assignment table specifically. I have been able to create foreign keys for employee and project tables under their respective tables, but am unsure how to create them for this specific table.

For trying to create the primary key, I was not given a primary key and am unsure how to proceed. My code thus far is:

ALTER TABLE dbo.ASSIGNMENT ADD CONSTRAINT ASSIGNMENT_PK PRIMARY KEY 
(ID);

The error message that pops up is: Msg 1911, Level 16, State 1, Line 1 Column name 'ID' does not exist in the target table or view. Msg 1750, Level 16, State 0, Line 1 Could not create constraint or index. See previous errors.

I am unsure how to create a primary key for this 'ASSIGNMENT' table because within the ERD provided by the instructor, there are 3 columns for ProjectID, EmployeeNumber, and Hours Worked. The first two are also foreign keys, but there is no explicit assignment pk given. Thus, I am unsure how to create a primary key for this table, and what the command would be under the ALTER table, as instructed by my instructor.

Any help understanding where I am going wrong on any of my codes, and guidance would be greatly appreciated! Thanks!

  • 1
    Your assignment probably tells you that the primary key for the assignment table should be a *compound* key on `ProjectID` and `EmployeeNumber`. However, that's just a guess since we don't know the task. – Damien_The_Unbeliever Feb 22 '22 at 08:36

1 Answers1

1

You cannot have ADD and also ALTER keywords in an ALTER TABLE !

The right syntax to add a constraint is :

ALTER TABLE dbo.ASSIGNMENT 
   ADD CONSTRAINT EMPLOYEE_FK 
       FOREIGN KEY (EmployeeNumber) 
       REFERENCES dbo.EMPLOYEE (EmployeeNumber);

This came from SQL ISO Standard SQL...

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • 1
    Yes but you cannot have simultanously ADD and ALTER. In this case you must first modify the datatype of the columns with an ALTER TABLE... ALTER COLUMN and then add the FK with an ALTER TABLE ... ADD CONSTRAINT.... – SQLpro Feb 22 '22 at 08:49
  • @serenity1300 There is nothing wrong with the syntax ALTER TABLE {table} ALTER COLUMN {column}{datatype}; but this syntax is NOT for adding a constraint but for altering a column's properties. To add a constraint use the syntax in this answer, to do both, you need 2 alter statements – GuidoG Feb 22 '22 at 09:49