Loading

cannot add foriegn key constraint

cannot add foriegn key constraint

vutharadi prathyusha

11y1.1kDatabases & DBA

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra11y ago
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.
0
vutharadi prathyusha11y ago
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..
0
Manas Mohapatra11y ago
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..
0