Re: How to have two not null constraints at the same time

Am 01.11.2010 13:44, schrieb A B:
> Hi.
> If I have a table
>
> country (id int Primary key, president bigint references people);
>
> and a table
> people (id bigint, country_id int not null references country );
>
> and now I want to enforce that there is a president in each country by
> adding a not null constraint on the column presient in the country
> table.
>
> Is there a way to do that? just adding the not null, will that not
> get me into a chicken-egg situation where I can not add a country
> without adding a president to the people table, which requires a
> country... and so on.
>
> So how would you insert the rows?
>
>
> Or are there other ways to do this without creating a new table to
> contain the presidents?
Right, you'll need some kind of null value.

Skip the not-null-constraint e.g. for the president_id in the countries
table at first.
Now you can insert a country with ID 0 and NULL as name for using as
default in the people.
Create the people table as intended and insert the first person with id
0 and country_id set to 0.

So there are save values for future inserts.
Now finish the country table with adding the skipped constraint.

ALTER TABLE countries ALTER COLUMN president_id SET NOT NULL;

From here on you start a transaction for every insert.
Add a country and fetch it's ID to create the corresponding president.
Update the country's president_id.
Commit the transaction.

Btw. your design is a two way 1:n constraint.
Every country can have n presidents while every entry in people could be
president of m countries.
C = ( ( 1, 'Country 1', 42 ), ( 2, 'Country 2', 42 ), ( 3, 'Country 3',
43 ) )
P = ( ( 42, 'Person 42', 1 ), ( 43, 'Person 43', 1 ), ( 44, 'Person 44',
3 ) )
This would be legal in your design.

If you want 1 president in 1 country at a time you might add a unique
constraint in both tables for the foreign key.
On the other hand that would bring back the chicken-egg-thingy. ;)