CODEWITHSUNDEEP
mysqlsqldatabase
sevcodes
sevcodes

Reputation: 45

MySQL database: Foreign key error

So I am trying to create a table which follows as:

CREATE TABLE gives (
pid VARCHAR(7),
crn VARCHAR(7),
PRIMARY KEY (pid, crn),
FOREIGN KEY (pid) REFERENCES professor(pid) ON DELETE CASCADE,
FOREIGN KEY (crn) REFERENCES course(crn) ON DELETE CASCADE
)

crn comes form course table which is:

CREATE TABLE course(
crn VARCHAR(7),
.........
PRIMARY KEY(crn, .....)
.........
)

pid comes from professor table which is:

CREATE TABLE professor(
pusername VARCHAR(20),
pid VARCHAR(7),
........
PRIMARY KEY(pusername, pid....),
FOREING KEY(pusername) REFERENCES b_user(username) ON DELETE CASCADE,
........
)

b_user follows as

CREATE TABLE b_user (
username VARCHAR(20),
........
PRIMARY KEY(username, .....)
........
)

However this 'gives' table gives me an error:

1005 - Can't create table courseweb.gives (errno: 150 "Foreign key constraint is incorrectly formed")

I have created types of gives before, never got an error. Only difference is this time I used those 'ON DELETE CASCADE's which gives a chance for chain deletion when I delete a user from b_user.

I have no idea why this is happening, I think my syntax is correct.

Upvotes: 1

Views: 94

Answers (3)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Try to create your table :

 CREATE TABLE gives (
    pid VARCHAR(7),
    crn VARCHAR(7),
    PRIMARY KEY (pid, crn))

Then add the constraints :

ALTER TABLE `gives`
  ADD CONSTRAINT `pid_c` FOREIGN KEY (`pid`) REFERENCES `professor` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `crn_c` FOREIGN KEY (`crn`) REFERENCES `course` (`crn`) ON DELETE CASCADE ON UPDATE CASCADE;

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15941

From the official docs here

InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

In other words, to have any reference/fk to professor.pid, professor must contain an index starting with pid.

Upvotes: 3

FJT
FJT

Reputation: 2083

Your FK references table professor, but you've incorrectly spelt the table as proffesor

Upvotes: 1

Related Questions