Do not make circular references on tables

Don't paint yourself in the corner. Do not make circular references on tables

Example:

create table country
(
country_code varchar(2) not null primary key,
country text not null 
);

create table person
(
person_code varchar(6) not null primary key,
person_name text not null,
birth_country_code
 varchar(2) null 
 references country(country_code)

);

alter table country
add column current_leader_code
 varchar(6) null 
 references person(person_code);

insert into country(country_code, country) values('PH','Philippines');
insert into person(person_code, person_name, birth_country_code) values('MB','Michael Buen', 'PH');
update country set current_leader_code = 'MB' where country_code = 'PH';

Even we can get away with making the two tables reference each other by strictly adhering to the sequence of the scripts above and making the country's current_leader_code nullable, we are painting ourselves in the corner by doing so.

When it's time for us to transfer this scripts to production machines, and we forgot to save those scripts; we will be left with this script(extracted from RDBMS admin tool):

create table country
(
country_code varchar(2) not null primary key,
country text not null,
current_leader_code 
 varchar(6) null
 references person(person_code)
);

create table person
(
person_code varchar(6) not null primary key,
person_name text not null,
birth_country_code
 varchar(2) null 
 references country(country_code)

);

That fails big time! How can that script execute?

So to design things properly for the case described above, we must introduce an association table:

create table country
(
country_code varchar(2) not null primary key,
country text not null
);

create table person
(
person_code varchar(6) not null primary key,
person_name text not null,
birth_country_code
 varchar(2) null 
 references country(country_code)

);



-- associations table
create table country_current_leader
(
dummy serial not null primary key,
country_code varchar(2) not null unique references country(country_code),
current_leader_code varchar(6) not null unique references person(person_code)
);


 
insert into country(country_code, country) values('PH','Philippines');
insert into country(country_code, country) values('CN','China');
insert into person(person_code, person_name, birth_country_code) values('MB','Michael Buen','PH');
insert into person(person_code, person_name, birth_country_code) values('HJ','Hu Jintao','CN');
insert into country_current_leader(country_code, current_leader_code) values('PH','MB');
insert into country_current_leader(country_code, current_leader_code) values('CN','HJ');
-- insert into country_current_leader(country_code, current_leader_code) values('CN','MB'); -- this will fail, only one leader per country, and only one country per leader
-- insert into country_current_leader(country_code, current_leader_code) values('PH','HJ'); -- this will fail too, only one leader per country, and only one country per leader