Thread: Non Identifying Foreign Key Relationships
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?
Thank you in advance for your help,
Karen Grose
kgrose@vigilos.com
kgrose@vigilos.com
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 forgottenthe cardinal rule that '' and NULL are not the same! I put NULL into the insert statements for the column in questionand 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?