CODEWITHSUNDEEP
mysqlsql
MD5
MD5

Reputation: 21

Can't create a table because a foreign key

create table hospitals(
    IDhospital int(2),
    name varchar(30),
    site varchar(30),
    foreign key (CODregion)
        references to regions(IDregion)
);

MySql says: the column "CODregion" doesn't exist in table

WHY??!!

Upvotes: 1

Views: 44

Answers (2)

switch
switch

Reputation: 116

Your regions table should look something like this -

create table regions (
IDregion int(2), 
name varchar(30),
PRIMARY KEY (IDregion));

You have to define IDregion as a primary key.

Now you can run the following query - 

create table hospitals (
IDhospital int(2), 
name varchar(30), 
site varchar(30), 
CODregion int(2),
foreign key (CODregion) references regions(IDregion));

In mysql you have to define the column in the create table syntax before using it as a foreign key constraint.

Hope this helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The foreign key declaration describes a column that has already been declared. So you want something like this:

create table hospitals (
    IDhospital int(2),
    name varchar(30),
    site varchar(30),
    CODregion int,
    foreign key (CODregion) references to regions(IDregion)
);

The type of CODregion needs to match the type of IDregion. This assumes the type is int.

Note: There really is no value to specifying a formatting length for int. Just use int.

Upvotes: 1

Related Questions