how is that possible

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>: