Cannot set foreign key in the table

Question

I have 2 tables.

login (login_id primary key 
Email varchar(254) not null
Password varchar(20) not null)

and

registration(
id PK
Email
password
name
... etc

)

I m trying to set email of login as foreign key and registration(email) as references. but I m not able to do it ALTER TABLE login ADD FOREIGN KEY(email) REFERENCES registraion(email);

I m using it for a project so that i cud match the login and registraion email while logging in as user. can someone plz help

What do you mean "not able to do `alter table...`"? Does it throw an error? If so, what does it say? — vesan, Oct 18 '15 at 09:50

David Soussan · Answer 1 · 2015-10-18T09:04:23.613

1

The column definitions in the 2 tables have to match for an FK to be created.

registration( id PK, Email VARCHAR(254) NOT NULL, password name

The datatype and length of the columns in both table must match.

edited Oct 18 '15 at 09:04

answered Oct 18 '15 at 08:58

David Soussan

2,698
1
16
19

both the columns are email – Archit Bhatia Oct 18 '15 at 08:59
VARCHAR(254) NOT NULL I mean, name does not matter in the least but the data type and length do – David Soussan Oct 18 '15 at 09:02
even they are the same – Archit Bhatia Oct 18 '15 at 09:03
well they are not the same in your code so what use is that, If you want good answers to your question include the code you are actually using. WE are not miond readers you know. So post your full table create statements please. – David Soussan Oct 18 '15 at 09:06

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

Foreign Key can be created by referencing a Primary Key on another table or a Unique Key with certain restriction such as column type and size. The type and size of both the columns must match.

For a more detailed discussion on the facts you may reference this SO post mysql-non-primary-foreign-key and this MySql reference manual create-table-foreign-keys

edited May 23 '17 at 12:03

Community

1
1

answered Oct 18 '15 at 10:09

haraman

2,744
2
27
50

Cannot set foreign key in the table

2 Answers2