CODEWITHSUNDEEP
mysqlforeign-keys
jscherman
jscherman

Reputation: 6189

ERROR 1215 (HY000): Cannot add foreign key constraint?

There are a million similar questions, but i tried everything and i can't find the solution. I have this tables : (Using MySQL)

create table users (
  user_id bigint unsigned not null auto_increment primary key,  
  username varchar(256) not null,
  password varchar(256) not null,
  enabled boolean
);
create unique index ix_users_name on users (username);

create table groups (
    group_id int unsigned not null auto_increment primary key,  
    group_name varchar(50) not null 
);
create unique index ix_groups_name on groups (group_name);

And i want to create this one.

create table group_members (
    group_member_id bigint unsigned not null auto_increment primary key,
    user_id bigint not null,
    group_id int not null,
    constraint fk_group_members_1 foreign key(user_id) references users(user_id),
    constraint fk_group_members_2 foreign key(group_id) references groups(group_id)
);
create unique index ix_member_group on group_members (user_id,group_id);

Then it throws 

ERROR 1215 (HY000): Cannot add foreign key constraint

Do you know what is going on?

Upvotes: 1

Views: 188

Answers (1)

Marc B
Marc B

Reputation: 360662

Your field definitions on BOTH sides of the FK must match:

t1: user_id bigint unsigned not null auto_increment primary key,  
                   ^^^^^^^^ 
t2: user_id bigint not null,

The t1 definition is unsigned, which means it's theoretically possible to create a record in t1 which cannot be represented in t2, as T1's range of possible IDs is 0->2^64, v.s. the -2^63-> + 2^63 in t2.

Upvotes: 3

Related Questions