Re: [HACKERS] serial as FK ?
On Mon, 28 Feb 2005, Richard Huxton wrote: Oleg Bartunov wrote: Hi there, what's wrong to use SERIAL as FK without explicit PRIMARY KEY or UNIQUE ? qq=# create table t1( id serial); NOTICE: CREATE TABLE will create implicit sequence t1_id_seq for serial column t1.id CREATE TABLE qq=# create table t2( id2 int4 references t1(id)); ERROR: there is no unique constraint matching given keys for referenced table t1 btw, Richard, I noticed this problem when looked into your example database http://www.archonet.com/pgdocs/chap-exdb.html#EXAMPLE-TABLES Ah - IIRC there *was* nothing wrong with it when those notes were written (and I need to update them, clearly). Back then SERIAL implied UNIQUE too, but that was changed (in 7.3 I believe). There are also some problems in examples. I think your notes are rather useful. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serial as FK ?
Oleg Bartunov wrote: Hi there, what's wrong to use SERIAL as FK without explicit PRIMARY KEY or UNIQUE ? qq=# create table t1( id serial); NOTICE: CREATE TABLE will create implicit sequence t1_id_seq for serial column t1.id CREATE TABLE qq=# create table t2( id2 int4 references t1(id)); ERROR: there is no unique constraint matching given keys for referenced table t1 btw, Richard, I noticed this problem when looked into your example database http://www.archonet.com/pgdocs/chap-exdb.html#EXAMPLE-TABLES Ah - IIRC there *was* nothing wrong with it when those notes were written (and I need to update them, clearly). Back then SERIAL implied UNIQUE too, but that was changed (in 7.3 I believe). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] serial as FK ?
On Mon, 28 Feb 2005, Oleg Bartunov wrote: what's wrong to use SERIAL as FK without explicit PRIMARY KEY or UNIQUE ? Serial isn't enough to guarantee uniqueness as required by foreign keys. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] serial as FK ?
On Mon, 28 Feb 2005, Stephan Szabo wrote: On Mon, 28 Feb 2005, Oleg Bartunov wrote: what's wrong to use SERIAL as FK without explicit PRIMARY KEY or UNIQUE ? Serial isn't enough to guarantee uniqueness as required by foreign keys. you're certainly right ! Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
