Error in SQL Syntax when creating simple foreign key reference

Question

Here's my script:

create table Country
(
CountryId int primary key,
Name varchar(255)
);

create table Person
(
PersonId int primary key,
Name varchar(255),
FOREIGN KEY (CountryId) references Country(CountryId)
);

I'm transitioning from MS SQL and trying to get a grasp on MySQL and starting off with my typical hello world of Person->Country relationship to get a feel for foreign keys.

I'm getting this error on PHPMyAdmin:

SQL query:

CREATE TABLE Person(

PersonId INT PRIMARY KEY , Name VARCHAR( 255 ) , FOREIGN KEY ( CountryId ) REFERENCES Country( CountryId ) );

MySQL said:

1072 - Key column 'CountryId' doesn't exist in table

What newbie mistake am I making here?

You changed the question after I answered! The new error is due to your second table not having a 'CountryId' field. — Matt Humphrey
– Matt Humphrey, Commented Sep 10, 2012 at 19:58
@MattHumphrey: So I have to declare the field explicitly in another line? — sergserg
– sergserg, Commented Sep 10, 2012 at 20:01

Adriano Carneiro · Accepted Answer · 2012-09-10 20:02:12Z

2

That's because you had not created the column in Person that would be used in the foreign key, thus, Key column 'CountryId' doesn't exist in table. Here's how you'd do it:

CREATE TABLE Person(
    PersonId INT PRIMARY KEY , 
    Name VARCHAR( 255 ) , 
    CountryId int,
    FOREIGN KEY ( CountryId ) REFERENCES Country( CountryId )
);

answered Sep 10, 2012 at 20:02

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sergserg Over a year ago

Can I write the creation and foreign key binding on the same line similar to MSSQL?

Adriano Carneiro Over a year ago

Not that I know of. But you can research here: dev.mysql.com/doc/refman/5.0/en/…

Collectives™ on Stack Overflow

Error in SQL Syntax when creating simple foreign key reference

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related