Non Identifying Foreign Key Relationships

On Mon, 24 Mar 2003, Karen Grose wrote:

I am in the process of upgrading to Postgresql 7.3.1 from 7.1. While
testing the results of the conversion, I have run into an issue with
establishing non-identifying or optional foreign key relationships.

1) My child table definition contains a foreign key that references the parent primary key
2) The child table column referencing the parent is not part of the child primary key
3) The child table column referencing the parent is defined with NULLS allowed

The test that I performed tried to load NULL values into the column
and it failed with a referential integrity violation stating that the
parent must exist. How do I accomplish creating this type of
constraint within Postgresql version 7.3?

That should work I think, can you give a standalone example script?

I apologize for wasting your time reading this post... I retested after seeing your reply and noticed that I had forgotten the cardinal rule that '' and NULL are not the same! I put NULL into the insert statements for the column in question and it worked as expected.

Thank you for your time,

Karen Grose
kgrose@vigilos.com
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Monday, March 24, 2003 4:36 PM
To: Karen Grose
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Non Identifying Foreign Key Relationships

On Mon, 24 Mar 2003, Karen Grose wrote:

I am in the process of upgrading to Postgresql 7.3.1 from 7.1. While
testing the results of the conversion, I have run into an issue with
establishing non-identifying or optional foreign key relationships.

1) My child table definition contains a foreign key that references the parent primary key
2) The child table column referencing the parent is not part of the child primary key
3) The child table column referencing the parent is defined with NULLS allowed

The test that I performed tried to load NULL values into the column
and it failed with a referential integrity violation stating that the
parent must exist. How do I accomplish creating this type of
constraint within Postgresql version 7.3?

That should work I think, can you give a standalone example script?