SQL error 1215(HY000) cannot add foreign key constraint.

Vote count:

0

I looked through other questions on this topic, and the general consensus was to check my data type consistency. I am trying to create the table 'reviews' that has SSN and ProjectID as foreign keys, referencing SSN from Table Reviewer and ProjectID from Table Project

CREATE TABLE Reviews(

ProjectID VARCHAR(10) NOT NULL,

SSN INTEGER NOT NULL CHECK

    (SSN>100000000 AND SSN<999999999),

ReviewerRole VARCHAR(50) NOT NULL,

FOREIGN KEY(SSN)REFERENCES Reviewer(SSN),

FOREIGN KEY(ProjectID)REFERENCES Project(ProjectID),

PRIMARY KEY(SSN,ProjectID)
);

The following are the create table statements for 'Project' and 'Reviewer'

CREATE TABLE Reviewer(
SSN INTEGER NOT NULL CHECK (SSN>100000000 AND SSN<999999999),
Firstname VARCHAR(50) NOT NULL,
Lastname VARCHAR(50) NOT NULL
);
CREATE TABLE Project(
ProjectID VARCHAR(10) NOT NULL,
Title VARCHAR(50),
Archived DATE NOT NULL,
ProjectStatus VARCHAR(50) NOT NULL,
PRIMARY KEY(ProjectID)
);

Project and Reviewer created without issue. My create Reviews query works if I remove the FK declaration for SSN and leave the FK declaration for ProjectID, but not the other way around.

What is the reason for the error? Is there another constraint I need to add?

asked 1 min ago

rmac1234

6

SQL error 1215(HY000) cannot add foreign key constraint.