Skip site navigation (1) Skip section navigation (2)

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

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to have two not null constraints at the same time
Date: 2010-11-01 16:10:04
Message-ID: 4CCEE65C.9020708@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
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.   ;)







In response to

pgsql-novice by date

Next:From: Majid AzimiDate: 2010-11-01 21:33:42
Subject: SELECT triggers - Table Partitioning
Previous:From: A BDate: 2010-11-01 12:44:56
Subject: How to have two not null constraints at the same time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group