Foreign Key Non-Null Problem in 8.0 - Mailing list pgsql-general
| From | Jon Uhal |
|---|---|
| Subject | Foreign Key Non-Null Problem in 8.0 |
| Date | |
| Msg-id | 000801c4bc34$17152700$3b0ba8c0@uhal2000 Whole thread Raw |
| List | pgsql-general |
I'm having trouble trying to get my databases setup so that when I delete a row from the base table, all related information is removed as well. I've been testing this with PostgreSQL version (postmaster (PostgreSQL) 8.0.0beta1) on a Windows 2000 Pro machine. I have been unsuccessful in trying to get the MATCH SIMPLE clause of CREATE TABLE to work and I was thinking it was because of how I had defined my tables. Every time I try to create a foreign key or reference into another table, postgres seems to make the column NOT NULL. I have been unable to get it to allow nulls in the foriegn key column.
Basically, my problem is that I have one table that is referenced from two different tables. In order to determine which table is the parent table of a particular row, I have two foriegn keys in the one table (RnetTestInfo in my code below), and one column has a reference and the other column will be null (or some invalid value such as -1). I have this working without the reference or foreign key constraint in place, but I want the database to handle cascading deletes. I also tried to solve this using rules, but because of the amount of information and the depth that is involved, postgres thinks that I am running into an infinite loop when trying to delete.
Here is a small example of the code that I'm trying to use. It's setup to provide an example of the tables that are created and the type of SQL I'm using that creates my problem.
--------------------------- START EXAMPLE --------------------------------
\c postgres
DROP DATABASE "JResultNet";
CREATE DATABASE "JResultNet";
\c "JResultNet"
CREATE TABLE RouteablePacket (field varchar(5), id serial NOT NULL, PRIMARY KEY (id));
CREATE TABLE RnetPacket (field varchar(5), foreign_index1 serial NOT NULL REFERENCES RouteablePacket (id) MATCH SIMPLE ON DELETE CASCADE, id serial UNIQUE, PRIMARY KEY (id));
CREATE TABLE MicroData (field varchar(5), foreign_index1 serial NOT NULL REFERENCES RnetPacket (id) MATCH SIMPLE ON DELETE CASCADE, id serial UNIQUE, PRIMARY KEY (id));
CREATE TABLE MicroTestInfo (field varchar(5), foreign_index1 serial NOT NULL REFERENCES MicroData (id) MATCH SIMPLE ON DELETE CASCADE, id serial, PRIMARY KEY (id));
CREATE TABLE RnetTestInfo (field varchar(5), foreign_index1 serial REFERENCES RnetPacket (id) MATCH SIMPLE ON DELETE CASCADE, foreign_index2 serial REFERENCES MicroTestInfo (id) MATCH SIMPLE ON DELETE CASCADE, id serial NOT NULL, PRIMARY KEY (id));
DROP DATABASE "JResultNet";
CREATE DATABASE "JResultNet";
\c "JResultNet"
CREATE TABLE RouteablePacket (field varchar(5), id serial NOT NULL, PRIMARY KEY (id));
CREATE TABLE RnetPacket (field varchar(5), foreign_index1 serial NOT NULL REFERENCES RouteablePacket (id) MATCH SIMPLE ON DELETE CASCADE, id serial UNIQUE, PRIMARY KEY (id));
CREATE TABLE MicroData (field varchar(5), foreign_index1 serial NOT NULL REFERENCES RnetPacket (id) MATCH SIMPLE ON DELETE CASCADE, id serial UNIQUE, PRIMARY KEY (id));
CREATE TABLE MicroTestInfo (field varchar(5), foreign_index1 serial NOT NULL REFERENCES MicroData (id) MATCH SIMPLE ON DELETE CASCADE, id serial, PRIMARY KEY (id));
CREATE TABLE RnetTestInfo (field varchar(5), foreign_index1 serial REFERENCES RnetPacket (id) MATCH SIMPLE ON DELETE CASCADE, foreign_index2 serial REFERENCES MicroTestInfo (id) MATCH SIMPLE ON DELETE CASCADE, id serial NOT NULL, PRIMARY KEY (id));
INSERT INTO RouteablePacket (field) VALUES ('a');
INSERT INTO RnetPacket (field, foreign_index1) VALUES ('b', 1);
INSERT INTO MicroData (field, foreign_index1) VALUES ('c', 1);
INSERT INTO RnetTestInfo (field, foreign_index1, foreign_index2) VALUES ('d', null, 1)
INSERT INTO RnetPacket (field, foreign_index1) VALUES ('b', 1);
INSERT INTO MicroData (field, foreign_index1) VALUES ('c', 1);
INSERT INTO RnetTestInfo (field, foreign_index1, foreign_index2) VALUES ('d', null, 1)
--------------------------- END EXAMPLE --------------------------------
When I run this in psql, I'm getting the output:
--------------------------- START EXAMPLE --------------------------------
JResultNet=# \i jresultnet.sql
You are now connected to database "postgres".
DROP DATABASE
CREATE DATABASE
You are now connected to database "JResultNet".
psql:jresultnet.sql:5: NOTICE: CREATE TABLE will create implicit sequence "routeablepacket_id_seq" for serial column "routeablepacket.id"
psql:jresultnet.sql:5: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "routeablepacket_pkey" for table "routeablepacket"
CREATE TABLE
psql:jresultnet.sql:6: NOTICE: CREATE TABLE will create implicit sequence "rnetpacket_foreign_index1_seq" for serial column "rnetpacket.foreign_index1"
psql:jresultnet.sql:6: NOTICE: CREATE TABLE will create implicit sequence "rnetpacket_id_seq" for serial column "rnetpacket.id"
psql:jresultnet.sql:6: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rnetpacket_pkey" for table "rnetpacket"
CREATE TABLE
psql:jresultnet.sql:7: NOTICE: CREATE TABLE will create implicit sequence "microdata_foreign_index1_seq" for serial column "microdata.foreign_index1"
psql:jresultnet.sql:7: NOTICE: CREATE TABLE will create implicit sequence "microdata_id_seq" for serial column "microdata.id"
psql:jresultnet.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "microdata_pkey" for table "microdata"
CREATE TABLE
psql:jresultnet.sql:8: NOTICE: CREATE TABLE will create implicit sequence "microtestinfo_foreign_index1_seq" for serial column "microtestinfo.foreign_index1"
psql:jresultnet.sql:8: NOTICE: CREATE TABLE will create implicit sequence "microtestinfo_id_seq" for serial column "microtestinfo.id"
psql:jresultnet.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "microtestinfo_pkey" for table "microtestinfo"
CREATE TABLE
psql:jresultnet.sql:9: NOTICE: CREATE TABLE will create implicit sequence "rnettestinfo_foreign_index1_seq" for serial column "rnettestinfo.foreign_index1"
psql:jresultnet.sql:9: NOTICE: CREATE TABLE will create implicit sequence "rnettestinfo_foreign_index2_seq" for serial column "rnettestinfo.foreign_index2"
psql:jresultnet.sql:9: NOTICE: CREATE TABLE will create implicit sequence "rnettestinfo_id_seq" for serial column "rnettestinfo.id"
psql:jresultnet.sql:9: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rnettestinfo_pkey" for table "rnettestinfo"
CREATE TABLE
INSERT 154276 1
INSERT 154277 1
INSERT 154278 1
psql:jresultnet.sql:14: ERROR: null value in column "foreign_index1" violates not-null constraint
You are now connected to database "postgres".
DROP DATABASE
CREATE DATABASE
You are now connected to database "JResultNet".
psql:jresultnet.sql:5: NOTICE: CREATE TABLE will create implicit sequence "routeablepacket_id_seq" for serial column "routeablepacket.id"
psql:jresultnet.sql:5: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "routeablepacket_pkey" for table "routeablepacket"
CREATE TABLE
psql:jresultnet.sql:6: NOTICE: CREATE TABLE will create implicit sequence "rnetpacket_foreign_index1_seq" for serial column "rnetpacket.foreign_index1"
psql:jresultnet.sql:6: NOTICE: CREATE TABLE will create implicit sequence "rnetpacket_id_seq" for serial column "rnetpacket.id"
psql:jresultnet.sql:6: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rnetpacket_pkey" for table "rnetpacket"
CREATE TABLE
psql:jresultnet.sql:7: NOTICE: CREATE TABLE will create implicit sequence "microdata_foreign_index1_seq" for serial column "microdata.foreign_index1"
psql:jresultnet.sql:7: NOTICE: CREATE TABLE will create implicit sequence "microdata_id_seq" for serial column "microdata.id"
psql:jresultnet.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "microdata_pkey" for table "microdata"
CREATE TABLE
psql:jresultnet.sql:8: NOTICE: CREATE TABLE will create implicit sequence "microtestinfo_foreign_index1_seq" for serial column "microtestinfo.foreign_index1"
psql:jresultnet.sql:8: NOTICE: CREATE TABLE will create implicit sequence "microtestinfo_id_seq" for serial column "microtestinfo.id"
psql:jresultnet.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "microtestinfo_pkey" for table "microtestinfo"
CREATE TABLE
psql:jresultnet.sql:9: NOTICE: CREATE TABLE will create implicit sequence "rnettestinfo_foreign_index1_seq" for serial column "rnettestinfo.foreign_index1"
psql:jresultnet.sql:9: NOTICE: CREATE TABLE will create implicit sequence "rnettestinfo_foreign_index2_seq" for serial column "rnettestinfo.foreign_index2"
psql:jresultnet.sql:9: NOTICE: CREATE TABLE will create implicit sequence "rnettestinfo_id_seq" for serial column "rnettestinfo.id"
psql:jresultnet.sql:9: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rnettestinfo_pkey" for table "rnettestinfo"
CREATE TABLE
INSERT 154276 1
INSERT 154277 1
INSERT 154278 1
psql:jresultnet.sql:14: ERROR: null value in column "foreign_index1" violates not-null constraint
--------------------------- END EXAMPLE --------------------------------
Thanks for any help in advance.
pgsql-general by date: