CODEWITHSUNDEEP
phpmysqldatabasephpmyadmin
stuckoverflow
stuckoverflow

Reputation: 11

Why won't my foreign key create in MySQL?

I've tried many different ways to create table with a foreign key and trying to insert into phpMyAdmin. However, it just not working as I was expected.

Here are what I've so far:

CREATE TABLE user (
    user_id BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    user_name VARCHAR(50) NOT NULL,
    user_password VARCHAR(50) NOT NULL);

This works perfectly fine. However, if I try to add a table with a foreign key thus, it refuses to create:

CREATE TABLE article (
    article_id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    article_title VARCHAR(100) NOT NULL,
    article_content VARCHAR(1000) NOT NULL,
    user_id INT(10) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (user_id));

This would not work as expected and would not add the table to the MySQL database. I get this error:

Cannot add foreign key constraint

How can I fix it?

Upvotes: 1

Views: 108

Answers (1)

halfer
halfer

Reputation: 20430

We discovered in the comments that if a primary key is defined thus:

user_id BIGINT(10) UNSIGNED

then a foreign key like this will not work, since it needs to match on signedness (and I think type too):

user_id INT(10) NOT NULL

This works fine:

user_id BIGINT(10) UNSIGNED NOT NULL

Here's a Fiddle to demonstrate it working.

Upvotes: 1

Related Questions