Thread: how is that possible
After a typo, I've just noticed the following : ~ 14:58:33: createdb test CREATE DATABASE ~ 14:58:42: psql test Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g orterminate with semicolon to execute query \q to quit test=# create table t1 (i int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE test=# create table t2 (i int references t2 1 on deley te cascade on update cascade ); CREATE TABLE test=# insert into t2 values (default); INSERT 0 1 test=# select * from t1;i --- (0 rows) test=# select * from t2;i --- (1 row) test=# \q should'nt the insert fail or have I mised something? postgresql 812 unixware 7.1.4 -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)
ohp@pyrenet.fr wrote: > test=# create table t1 (i int primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" > CREATE TABLE > test=# create table t2 (i int references t2 1 on deley te cascade on update cascade > ); The t2.i column is nullable. There is no bug here. Declare NOT NULL if that's what you want. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: > After a typo, I've just noticed the following : > > ~ 14:58:33: createdb test > CREATE DATABASE > ~ 14:58:42: psql test > Welcome to psql 8.1.2, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > test=# create table t1 (i int primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" > CREATE TABLE > test=# create table t2 (i int references t2 1 on deley te cascade on update cascade > ); > CREATE TABLE > test=# insert into t2 values (default); > INSERT 0 1 > test=# select * from t1; > i > --- > (0 rows) > > test=# select * from t2; > i > --- > > (1 row) > > test=# \q > > should'nt the insert fail or have I mised something? Why do you think it should have failed? It looks okay to me.
My understanding is that null or not, their should have been a foreign key violation. Maybe I misunderstood. On Fri, 10 Feb 2006, Stephan Szabo wrote: > Date: Fri, 10 Feb 2006 06:48:02 -0800 (PST) > From: Stephan Szabo <sszabo@megazone.bigpanda.com> > To: ohp@pyrenet.fr > Cc: pgsql-hackers list <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] how is that possible > > > On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: > > > After a typo, I've just noticed the following : > > > > ~ 14:58:33: createdb test > > CREATE DATABASE > > ~ 14:58:42: psql test > > Welcome to psql 8.1.2, the PostgreSQL interactive terminal. > > > > Type: \copyright for distribution terms > > \h for help with SQL commands > > \? for help with psql commands > > \g or terminate with semicolon to execute query > > \q to quit > > > > test=# create table t1 (i int primary key); > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" > > CREATE TABLE > > test=# create table t2 (i int references t2 1 on deley te cascade on update cascade > > ); > > CREATE TABLE > > test=# insert into t2 values (default); > > INSERT 0 1 > > test=# select * from t1; > > i > > --- > > (0 rows) > > > > test=# select * from t2; > > i > > --- > > > > (1 row) > > > > test=# \q > > > > should'nt the insert fail or have I mised something? > > Why do you think it should have failed? It looks okay to me. > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)
On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: > My understanding is that null or not, their should have been a foreign key > violation. Not as far as I can tell. MATCH (without partial or full) returns true if any column in the row value constructor is NULL. MATCH FULL returns true if all columns in the row value constructor are NULL and returns false if it's a mix of NULLs and non-NULLs.
Many thanks for explaining. I learned something today... On Fri, 10 Feb 2006, Stephan Szabo wrote: > Date: Fri, 10 Feb 2006 08:59:51 -0800 (PST) > From: Stephan Szabo <sszabo@megazone.bigpanda.com> > To: ohp@pyrenet.fr > Cc: pgsql-hackers list <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] how is that possible > > > On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: > > > My understanding is that null or not, there should have been a foreign key > > violation. > > Not as far as I can tell. MATCH (without partial or full) returns true if > any column in the row value constructor is NULL. MATCH FULL returns true > if all columns in the row value constructor are NULL and returns false if > it's a mix of NULLs and non-NULLs. > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)
IMHO null values shouldn't verify foreign keys constraints... Gustavo. 2006/2/10, ohp@pyrenet.fr <ohp@pyrenet.fr>: > Many thanks for explaining. > I learned something today... > > On Fri, 10 Feb 2006, Stephan Szabo wrote: > > > Date: Fri, 10 Feb 2006 08:59:51 -0800 (PST) > > From: Stephan Szabo <sszabo@megazone.bigpanda.com> > > To: ohp@pyrenet.fr > > Cc: pgsql-hackers list <pgsql-hackers@postgresql.org> > > Subject: Re: [HACKERS] how is that possible > > > > > > On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: > > > > > My understanding is that null or not, there should have been a foreign > key > > > violation. > > > > Not as far as I can tell. MATCH (without partial or full) returns true if > > any column in the row value constructor is NULL. MATCH FULL returns true > > if all columns in the row value constructor are NULL and returns false if > > it's a mix of NULLs and non-NULLs. > > > > > > -- > Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) > 15, Chemin des Monges +33-5-61-50-97-01 (Fax) > 31190 AUTERIVE +33-6-07-63-80-64 (GSM) > FRANCE Email: ohp@pyrenet.fr > ------------------------------------------------------------------------------ > Make your life a dream, make your dream a reality. (St Exupery) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >