|
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=# \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. |
