CODEWITHSUNDEEP
sqlforeign-keys
Aaron Lyall
Aaron Lyall

Reputation: 41

Foreign Key Constraint SQL

Keep getting the error cannot add foreign key constraint, any ideas probably a noob mistake but i am new to SQL.

CREATE TABLE tblDept (

  Dept VARCHAR(50),
  Name VARCHAR(50)
  );

CREATE TABLE tblPersonnel (

  PersID INT PRIMARY KEY,
  FullName VARCHAR(100),
  Address VARCHAR(100),
  Dept VARCHAR(50),
  Grade VARCHAR (2),
  Salary INT,
  DateOfBirth DATE,
  Manager INT,
  FOREIGN KEY(Dept) REFERENCES tblDept(Dept)

  );

 CREATE TABLE PersonnelPhone (

   PersID INT, 
   Extension INT,
   FOREIGN KEY (PersID) REFERENCES tblPersonnel(PersID)
   );

 CREATE TABLE tblPhone (
   Extension INT,
   DeviceType VARCHAR,
   VoiceMail BOOLEAN,
   FOREIGN KEY (Extension) REFERENCES tblPersonnelPhone(Extension)
   );

Upvotes: 0

Views: 84

Answers (2)

Max
Max

Reputation: 106

There are several errors in there. Here's a working schema definition:

CREATE TABLE tblDept (
  Dept VARCHAR(50) PRIMARY KEY, 
  Name VARCHAR(50)
);

CREATE TABLE tblPersonnel (
  PersID INT PRIMARY KEY, 
  FullName VARCHAR(100), 
  Address VARCHAR(100), 
  Dept VARCHAR(50), 
  Grade VARCHAR(2), 
  Salary INT, 
  DateOfBirth DATE, 
  Manager INT, 
  FOREIGN KEY(Dept) REFERENCES tblDept(Dept)
);

CREATE TABLE tblPersonnelPhone (
  PersID INT, 
  Extension INT PRIMARY KEY, 
  FOREIGN KEY (PersID) REFERENCES tblPersonnel(PersID)
);

CREATE TABLE tblPhone (
  Extension INT, 
  DeviceType VARCHAR(100), 
  VoiceMail BOOLEAN, 
  FOREIGN KEY (Extension) REFERENCES tblPersonnelPhone(Extension)
);

For future reference, use SQLFiddle for sharing these types of problems: http://sqlfiddle.com/#!9/2ce29

Upvotes: 2

George Sovetov
George Sovetov

Reputation: 5238

Extension in second table must be PRIMARY KEY.

You probably want to implement many-to-many relationship. In this case, Extension in PersonnelPhone should be FOREIGN KEY and Extension in tblPhone should be PRIMARY KEY.

Upvotes: 2

Related Questions