CODEWITHSUNDEEP
mysqlforeign-keys
taylorni
taylorni

Reputation: 25

Foreign Key Error - Mysql

I have these two tables:

CREATE TABLE tbl_member ( ID int(11) NOT NULL AUTO_INCREMENT, MemberNo varchar(30) DEFAULT '', Surname varchar(40) DEFAULT NULL, GivenNames varchar(30) DEFAULT NULL, PRIMARY KEY (ID), );

CREATE TABLE tbl_sale ( ID int(11) NOT NULL AUTO_INCREMENT, TxnType smallint(2) NOT NULL, SalesID varchar(20) NOT NULL, Reference int(11) NOT NULL, TxnDate datetime NOT NULL, MemberID int(11) DEFAULT NULL, PRIMARY KEY (ID), KEY MemberIDX (MemberID), KEY TxnDateIDX (TxnDate) );

But whenever I try to add this

ALTER TABLE tbl_sale ADD CONSTRAINT tbl_sale_fk1 FOREIGN KEY (MemberID) REFERENCES tbl_member (ID);

I am getting this error

Error Code: 1215. Cannot add foreign key constraint

I don't understand why I am getting this error, Any help is appreciate

Thanks

Upvotes: 0

Views: 126

Answers (1)

Alexandru Cimpanu
Alexandru Cimpanu

Reputation: 1069

You are not allowed to have MemberID int(11) DEFAULT NULL. It must be NOT NULL.

Upvotes: 0

Related Questions