X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EAAEB3A3ADF for ; Wed, 27 Oct 2004 16:11:28 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90490-01 for ; Wed, 27 Oct 2004 15:11:22 +0000 (GMT) Received: from dawnmail.dawning.com (dawnmail.dawning.com [24.97.87.106]) by svr1.postgresql.org (Postfix) with ESMTP id 37DF43A3C3B for ; Wed, 27 Oct 2004 16:11:21 +0100 (BST) Received: from uhal2000 (unknown [192.168.11.59]) by dawnmail.dawning.com (Postfix) with SMTP id D9E635E4016 for ; Wed, 27 Oct 2004 11:11:20 -0400 (EDT) Message-ID: <001d01c4bc36$faa13020$3b0ba8c0@uhal2000> From: "Jon Uhal" To: Subject: Foreign Key Non-Null Problem in 8.0 Date: Wed, 27 Oct 2004 11:09:39 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001A_01C4BC15.738AFC40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200410/213 X-Sequence-Number: 19316 This is a multi-part message in MIME format. ------=_NextPart_000_001A_01C4BC15.738AFC40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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)); 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) --------------------------- END EXAMPLE -------------------------------- When I run this in psql, I'm getting the output: --------------------------- START EXAMPLE = -------------------------------- JResultNet=3D# \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 --------------------------- END EXAMPLE -------------------------------- Thanks for any help in advance. ------=_NextPart_000_001A_01C4BC15.738AFC40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm having trouble trying to get my = databases setup=20 so that when I delete a row from the base table, all related information = is=20 removed as well.  I've been testing this with PostgreSQL version=20 (postmaster (PostgreSQL) 8.0.0beta1) on a Windows 2000 Pro = machine.  I have=20 been unsuccessful in trying to get the MATCH SIMPLE clause of CREATE = TABLE to=20 work and I was thinking it was because of how I had defined my = tables. =20 Every time I try to create a foreign key or reference into another = table,=20 postgres seems to make the column NOT NULL.  I have been unable to = get it=20 to allow nulls in the foriegn key column.
 
Basically, my problem is that I have = one table that=20 is referenced from two different tables.  In order to determine = which table=20 is the parent table of a particular row, I have two foriegn keys in the = one=20 table (RnetTestInfo in my code below), and one column has a reference = and the=20 other column will be null (or some invalid value such as -1).  I = have this=20 working without the reference or foreign key constraint in place, but I = want the=20 database to handle cascading deletes.  I also tried to solve this = using=20 rules, but because of the amount of information and the depth that is = involved,=20 postgres thinks that I am running into an infinite loop when trying to=20 delete.
 
Here is a small example of the code = that I'm trying=20 to use.  It's setup to provide an example of the tables that are = created=20 and the type of SQL I'm using that creates my problem.
 
--------------------------- START = EXAMPLE=20 --------------------------------
\c postgres
DROP DATABASE=20 "JResultNet";
CREATE DATABASE "JResultNet";
\c = "JResultNet"
CREATE=20 TABLE RouteablePacket (field varchar(5), id serial NOT NULL, PRIMARY KEY = (id));
CREATE TABLE RnetPacket (field varchar(5), foreign_index1 = serial NOT=20 NULL REFERENCES RouteablePacket (id) MATCH SIMPLE ON DELETE CASCADE, id = serial=20 UNIQUE, PRIMARY KEY (id));
CREATE TABLE MicroData (field varchar(5),=20 foreign_index1 serial NOT NULL REFERENCES RnetPacket (id) MATCH SIMPLE = ON DELETE=20 CASCADE, id serial UNIQUE, PRIMARY KEY (id));
CREATE TABLE = MicroTestInfo=20 (field varchar(5), foreign_index1 serial NOT NULL REFERENCES MicroData = (id)=20 MATCH SIMPLE ON DELETE CASCADE, id serial, PRIMARY KEY (id));
CREATE = TABLE=20 RnetTestInfo (field varchar(5), foreign_index1 serial REFERENCES = RnetPacket (id)=20 MATCH SIMPLE ON DELETE CASCADE, foreign_index2 serial REFERENCES = MicroTestInfo=20 (id) MATCH SIMPLE ON DELETE CASCADE, id serial NOT NULL, PRIMARY KEY=20 (id));
 
INSERT INTO RouteablePacket (field) = VALUES=20 ('a');
INSERT INTO RnetPacket (field, foreign_index1) VALUES ('b',=20 1);
INSERT INTO MicroData (field, foreign_index1) VALUES ('c', = 1);
INSERT=20 INTO RnetTestInfo (field, foreign_index1, foreign_index2) VALUES ('d', = null,=20 1)
--------------------------- END EXAMPLE = --------------------------------
 
 
When I run this in psql, I'm getting = the=20 output:
--------------------------- START = EXAMPLE=20 --------------------------------
JResultNet=3D# \i jresultnet.sql
You = are now=20 connected to database "postgres".
DROP DATABASE
CREATE = DATABASE
You are=20 now connected to database "JResultNet".
psql:jresultnet.sql:5: = NOTICE: =20 CREATE TABLE will create implicit sequence "routeablepacket_id_seq" for = serial=20 column "routeablepacket.id"
psql:jresultnet.sql:5: NOTICE:  = CREATE TABLE=20 / PRIMARY KEY will create implicit index "routeablepacket_pkey" for = table=20 "routeablepacket"
CREATE TABLE
psql:jresultnet.sql:6: = NOTICE:  CREATE=20 TABLE will create implicit sequence "rnetpacket_foreign_index1_seq" for = serial=20 column "rnetpacket.foreign_index1"
psql:jresultnet.sql:6: = NOTICE: =20 CREATE TABLE will create implicit sequence "rnetpacket_id_seq" for = serial column=20 "rnetpacket.id"
psql:jresultnet.sql:6: NOTICE:  CREATE TABLE / = PRIMARY=20 KEY will create implicit index "rnetpacket_pkey" for table=20 "rnetpacket"
CREATE TABLE
psql:jresultnet.sql:7: NOTICE:  = CREATE=20 TABLE will create implicit sequence "microdata_foreign_index1_seq" for = serial=20 column "microdata.foreign_index1"
psql:jresultnet.sql:7: = NOTICE:  CREATE=20 TABLE will create implicit sequence "microdata_id_seq" for serial column = "microdata.id"
psql:jresultnet.sql:7: NOTICE:  CREATE TABLE / = PRIMARY=20 KEY will create implicit index "microdata_pkey" for table = "microdata"
CREATE=20 TABLE
psql:jresultnet.sql:8: NOTICE:  CREATE TABLE will create = implicit=20 sequence "microtestinfo_foreign_index1_seq" for serial column=20 "microtestinfo.foreign_index1"
psql:jresultnet.sql:8: NOTICE:  = CREATE=20 TABLE will create implicit sequence "microtestinfo_id_seq" for serial = column=20 "microtestinfo.id"
psql:jresultnet.sql:8: NOTICE:  CREATE TABLE = /=20 PRIMARY KEY will create implicit index "microtestinfo_pkey" for table=20 "microtestinfo"
CREATE TABLE
psql:jresultnet.sql:9: NOTICE:  = CREATE=20 TABLE will create implicit sequence "rnettestinfo_foreign_index1_seq" = for serial=20 column "rnettestinfo.foreign_index1"
psql:jresultnet.sql:9: = NOTICE: =20 CREATE TABLE will create implicit sequence = "rnettestinfo_foreign_index2_seq" for=20 serial column "rnettestinfo.foreign_index2"
psql:jresultnet.sql:9:=20 NOTICE:  CREATE TABLE will create implicit sequence = "rnettestinfo_id_seq"=20 for serial column "rnettestinfo.id"
psql:jresultnet.sql:9: = NOTICE: =20 CREATE TABLE / PRIMARY KEY will create implicit index = "rnettestinfo_pkey" for=20 table "rnettestinfo"
CREATE TABLE
INSERT 154276 1
INSERT 154277 = 1
INSERT 154278 1
psql:jresultnet.sql:14: ERROR:  null value = in=20 column "foreign_index1" violates not-null constraint
--------------------------- END EXAMPLE = --------------------------------
 

Thanks for any help in=20 advance.
------=_NextPart_000_001A_01C4BC15.738AFC40--