cannot add foriegn key constraint

Question

Hi In MySql command prompt i getting this error.Cannot add foriegn key constraint . CREATE TABLE Contibutors ( Id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY MagazineId INT(6) NOT NULL Name VARCHAR(50) NOT NULL Email VARCHAR(100) Guid VARCHAR(36) NOT NULL ContentSubmitted BIT CreatedOn DATETIME FOREIGN KEY (MagazineId) REFERENCES Magazine(Id) ); If i do any thing wrong here.Will you please tell me. Thanks prathyusha

Manas Mohapatra · Answer

Hi Please find below checklists: 1. Make sure that table and column exists before creating foreign key. 2. Both columns (Id and MagazineId) should have same datatype. Means if Id column of Magazine table is int then MagaineId column data type should be int. I am anticipating that Id column of Magazine table is unsigned then MagazineId should be INT unsigned not INT only. If so then please modify MagazineId column datatype to INT Unsigned(as per your Id column Magazine table). Please recheck it.

vutharadi prathyusha · Answer

Hi Sory this query is not working.Cannot add Foriegn key constaint iam getting this error.Code is Shown below. CREATE TABLE Contibutors ( Id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY MagazineId INT(6) NOT NULL  Name VARCHAR(50) NOT NULL Email VARCHAR(100) Guid VARCHAR(36) NOT NULL ContentSubmitted BIT CreatedOn DATETIME FOREIGN KEY fk_Contributors(MagazineId) REFERENCES Magazine(Id) ); Will please tell me..

Manas Mohapatra · Answer

Hi In order to resolve your problem please find below solutions: 1. Looks your query is correct. For trail basis change your query: FOREIGN KEY fk_Magazine(MagazineId) REFERENCES Magazine(Id) 2. Secondly if Magazine table is already exist then make sure that Id column doesn't contain NULL value and Id column is exit. Please provide exact error what you are getting..