CODEWITHSUNDEEP
sqlsql-server-2008
user2265713
user2265713

Reputation: 21

Error: Could not create constraint SQL Server 2008

I am creating 6 tables. When creating the first 3 tables, everything works well. But then when I have to use a Foreign Key that I have used previously when creating another table, the system presents an error.

These are the 3 first tables:

CREATE TABLE Employee (
  EmployeeID INTEGER PRIMARY KEY NOT NULL,
  Name TEXT NOT NULL,
  Position TEXT NOT NULL,
  Salary REAL NOT NULL,
  Remarks TEXT
);

CREATE TABLE Planet (
  PlanetID INTEGER PRIMARY KEY NOT NULL,
  Name TEXT NOT NULL,
  Coordinates REAL NOT NULL
);

CREATE TABLE Has_Clearance (
  Employee INTEGER NOT NULL
    CONSTRAINT fk_Employee_EmployeeID REFERENCES Employee(EmployeeID),
  Planet INTEGER NOT NULL
    CONSTRAINT fk_Planet_PlanetID REFERENCES Planet(PlanetID),
  Level INTEGER NOT NULL,
  PRIMARY KEY(Employee, Planet)
);

Then I create the 4th table:

CREATE TABLE Shipment (
  ShipmentID INTEGER PRIMARY KEY NOT NULL,
  Date TEXT,
  Manager INTEGER NOT NULL
    CONSTRAINT fk_Employee_EmployeeID REFERENCES Employee(EmployeeID),
  Planet INTEGER NOT NULL
    CONSTRAINT fk_Planet_PlanetID REFERENCES Planet(PlanetID)
);

And I get the following error: "There is already an object named 'fk_Employee_EmployeeID' in the database. Could not create constraint."

Please let me know how to create the FK in this 4th table.

Upvotes: 2

Views: 20523

Answers (1)

dferidarov
dferidarov

Reputation: 602

The constraint "fk_Employee_EmployeeID" is first created on table "Has_Clearance" and then you try to create another constraint with the same name - which is not allowed. Just rename the constraint in the 4-th table like this:

CREATE TABLE Shipment ( 
ShipmentID INTEGER PRIMARY KEY NOT NULL, 
Date TEXT, 
Manager INTEGER NOT NULL CONSTRAINT fk_Maneger_EmployeeID REFERENCES Employee(EmployeeID), 
Planet INTEGER NOT NULL CONSTRAINT fk_Planet_PlanetID REFERENCES Planet(PlanetID)
);

Upvotes: 2

Related Questions