Foreign Key Constraint SQL

Question

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) );

George Sovetov · Accepted Answer · 2016-03-08 21:01:15Z

2

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.

edited Mar 8, 2016 at 21:01

answered Mar 8, 2016 at 20:57

George Sovetov

5,3286 gold badges43 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Philip Kelley Over a year ago

Ditto, Dept in first table, used by foreign key defined in second table.

Aaron Lyall Over a year ago

Thank you very much for for your help! makes sense now, i lost hours to this haha

Max · Accepted Answer · 2016-03-08 21:08:52Z

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

Thank you very much for your help and time its highly appreciated, Also thanks i will bookmark that site.

Collectives™ on Stack Overflow

Foreign Key Constraint SQL

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related