How do I add foreign Key to mariadb?

August 27, 2023

I am trying to create a table in mariadb, but each time I try adding a foreign key it returns an error message. When I try to create the table without the foreign key it works.
Here is the code without the foreign key that executes.

create table countries(
   country_id int(249) auto_increment primary key,
   name varchar(50) not null,
   area decimal(10,2) not null,
   national_day date,
   region_id int not null);

Here is the sql where I add the foreign key

create table countries(
   country_id int(249) auto_increment primary key,
   name varchar(50) not null,
   area decimal(10,2) not null,
   national_day date,
   region_id int not null,
   foreign key(region_id)
);

the error I get when I try it is the following
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
i have also tried

create table countries(
   country_id int(249) auto_increment primary key,
   name varchar(50) not null,
   area decimal(10,2) not null,
   national_day date,
   region_id int not null foreign key
);

it still gives me the error, I feel that it should be easy to add a foreign key but I am lost.

>Solution :

the manual tells you the correct form of the foreign key

CREATE tABLe region(id int Primary KEY auto_increment);

create table countries(
   country_id int(249) auto_increment primary key,
   name varchar(50) not null,
   area decimal(10,2) not null,
   national_day date,
   region_id int not null,
   foreign key(region_id) references region(id)
);

fiddle