Thread: Nullable 'Foreign Key-like' Constraint
I posted this to 'questions' yesterday instead of 'general' by mistake.
Sorry if anyone received duplicates.
----------------------------------------------------
Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM
I want to add a 'nullable' foreign key to a column in a table. I have
tables "company" and "project" which may be related by
company.companyID <-> project.companyID.
project.companyID is allowed to be null. However, when someone tries to
delete a company which is still referenced in "project" I want a
constraint restricting deletion.
I tried:
ALTER TABLE company ADD CONSTRAINT company_is_ta
CHECK (companyID IN
(SELECT companyID FROM project));
and I receive:
ERROR: cannot use subselect in CHECK constraint expression
Then I came across this previous post which showed how to set it up when
the table is created. I tried it and it works for a new table, but I
can't get it to work with existing tables.
1) My attempt:
ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL;
ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
REFERENCES company(companyID);
(plus variations on the above, resulting in errors, all similar to:)
ERROR: parser: parse error at or near "companyID" at character 53
2) based on this previous posting:
> From: Manfred Koizar (mkoi-pg@aon.at)
> Subject: Re: NULL Foreign Key
> Newsgroups:comp.databases.postgresql.general,
> comp.databases.postgresql.questions
> Date: 2002-07-17 05:51:19 PST
> On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
> <KuhnDK@navair.navy.mil> wrote:
> >Can I make a foreign key that is allowed to be NULL?
> Yes:
> fred=# CREATE TABLE father (i INT PRIMARY KEY);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'father_pkey' for table 'father'
> CREATE
> fred=# CREATE TABLE son (i INT REFERENCES father);
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> fred=# INSERT INTO father VALUES (1);
> INSERT 183317 1
> fred=# INSERT INTO son VALUES (1);
> INSERT 183318 1
> fred=# INSERT INTO son VALUES (2);
> ERROR: <unnamed> referential integrity violation - key referenced
> from son not found in father
> fred=# INSERT INTO son VALUES (NULL);
> INSERT 183320 1
> Servus
> Manfred
Anyone know how I can get this to work? BTW I don't want to use 'ignore'
rules when someone attempts to delete the company as I want the
constraint message to be shown in the app's browser.
TIA
Ron
Ron,
I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the
foreignkey... works like a charm:
CREATE TABLE TESTTYPE (
TESTTYPEKEY char(30) NOT NULL,
TESTTYPENAME varchar(255) NULL,
TESTTYPEDESC varchar(255) NULL,
TESTTYPELABEL varchar(255) NULL,
CONSTRAINT XPKTESTTYPE
PRIMARY KEY (TESTTYPEKEY)
)
;
CREATE TABLE TEST (
TESTKEY char(30) NOT NULL,
TESTTYPEKEY char(30) NULL,
CONSTRAINT LOG_PK
PRIMARY KEY (TEST_PK),
CONSTRAINT testtype_test
FOREIGN KEY (TESTTYPEKEY)
REFERENCES TESTTYPE
)
;
Karen L. Grose
Vigilos Inc.
-----Original Message-----
From: Ron [mailto:rstpATlin@uxwav.esDOTcom]
Sent: Thursday, October 23, 2003 9:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Nullable 'Foreign Key-like' Constraint
I posted this to 'questions' yesterday instead of 'general' by mistake.
Sorry if anyone received duplicates.
----------------------------------------------------
Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM
I want to add a 'nullable' foreign key to a column in a table. I have
tables "company" and "project" which may be related by
company.companyID <-> project.companyID.
project.companyID is allowed to be null. However, when someone tries to
delete a company which is still referenced in "project" I want a
constraint restricting deletion.
I tried:
ALTER TABLE company ADD CONSTRAINT company_is_ta
CHECK (companyID IN
(SELECT companyID FROM project));
and I receive:
ERROR: cannot use subselect in CHECK constraint expression
Then I came across this previous post which showed how to set it up when
the table is created. I tried it and it works for a new table, but I
can't get it to work with existing tables.
1) My attempt:
ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL;
ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
REFERENCES company(companyID);
(plus variations on the above, resulting in errors, all similar to:)
ERROR: parser: parse error at or near "companyID" at character 53
2) based on this previous posting:
> From: Manfred Koizar (mkoi-pg@aon.at)
> Subject: Re: NULL Foreign Key
> Newsgroups:comp.databases.postgresql.general,
> comp.databases.postgresql.questions
> Date: 2002-07-17 05:51:19 PST
> On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
> <KuhnDK@navair.navy.mil> wrote:
> >Can I make a foreign key that is allowed to be NULL?
> Yes:
> fred=# CREATE TABLE father (i INT PRIMARY KEY);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'father_pkey' for table 'father'
> CREATE
> fred=# CREATE TABLE son (i INT REFERENCES father);
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> fred=# INSERT INTO father VALUES (1);
> INSERT 183317 1
> fred=# INSERT INTO son VALUES (1);
> INSERT 183318 1
> fred=# INSERT INTO son VALUES (2);
> ERROR: <unnamed> referential integrity violation - key referenced
> from son not found in father
> fred=# INSERT INTO son VALUES (NULL);
> INSERT 183320 1
> Servus
> Manfred
Anyone know how I can get this to work? BTW I don't want to use 'ignore'
rules when someone attempts to delete the company as I want the
constraint message to be shown in the app's browser.
TIA
Ron
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Ron,
I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the
foreignkey... works like a charm:
CREATE TABLE TESTTYPE (
TESTTYPEKEY char(30) NOT NULL,
TESTTYPENAME varchar(255) NULL,
TESTTYPEDESC varchar(255) NULL,
TESTTYPELABEL varchar(255) NULL,
CONSTRAINT XPKTESTTYPE
PRIMARY KEY (TESTTYPEKEY)
)
;
CREATE TABLE TEST (
TESTKEY char(30) NOT NULL,
TESTTYPEKEY char(30) NULL,
CONSTRAINT LOG_PK
PRIMARY KEY (TEST_PK),
CONSTRAINT testtype_test
FOREIGN KEY (TESTTYPEKEY)
REFERENCES TESTTYPE
)
;
Karen L. Grose
Vigilos Inc.
Karen L. Grose
Vigilos Inc.
2030 First Avenue
Suite 300
Seattle, WA 98121
206.728.6464 ext. 111 :Phone
206.728.6440 :Fax
206.335-8386 :Cell
-----Original Message-----
From: Ron [mailto:rstpATlin@uxwav.esDOTcom]
Sent: Thursday, October 23, 2003 9:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Nullable 'Foreign Key-like' Constraint
I posted this to 'questions' yesterday instead of 'general' by mistake.
Sorry if anyone received duplicates.
----------------------------------------------------
Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM
I want to add a 'nullable' foreign key to a column in a table. I have
tables "company" and "project" which may be related by
company.companyID <-> project.companyID.
project.companyID is allowed to be null. However, when someone tries to
delete a company which is still referenced in "project" I want a
constraint restricting deletion.
I tried:
ALTER TABLE company ADD CONSTRAINT company_is_ta
CHECK (companyID IN
(SELECT companyID FROM project));
and I receive:
ERROR: cannot use subselect in CHECK constraint expression
Then I came across this previous post which showed how to set it up when
the table is created. I tried it and it works for a new table, but I
can't get it to work with existing tables.
1) My attempt:
ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL;
ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
REFERENCES company(companyID);
(plus variations on the above, resulting in errors, all similar to:)
ERROR: parser: parse error at or near "companyID" at character 53
2) based on this previous posting:
> From: Manfred Koizar (mkoi-pg@aon.at)
> Subject: Re: NULL Foreign Key
> Newsgroups:comp.databases.postgresql.general,
> comp.databases.postgresql.questions
> Date: 2002-07-17 05:51:19 PST
> On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
> <KuhnDK@navair.navy.mil> wrote:
> >Can I make a foreign key that is allowed to be NULL?
> Yes:
> fred=# CREATE TABLE father (i INT PRIMARY KEY);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'father_pkey' for table 'father'
> CREATE
> fred=# CREATE TABLE son (i INT REFERENCES father);
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> fred=# INSERT INTO father VALUES (1);
> INSERT 183317 1
> fred=# INSERT INTO son VALUES (1);
> INSERT 183318 1
> fred=# INSERT INTO son VALUES (2);
> ERROR: <unnamed> referential integrity violation - key referenced
> from son not found in father
> fred=# INSERT INTO son VALUES (NULL);
> INSERT 183320 1
> Servus
> Manfred
Anyone know how I can get this to work? BTW I don't want to use 'ignore'
rules when someone attempts to delete the company as I want the
constraint message to be shown in the app's browser.
TIA
Ron
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Thu, 23 Oct 2003 16:02:03 GMT, Ron <rstpATlin@uxwav.esDOTcom>
wrote:
> ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
> REFERENCES company(companyID);
> (plus variations on the above, resulting in errors, all similar to:)
> ERROR: parser: parse error at or near "companyID" at character 53
ALTER TABLE project ADD CONSTRAINT company_is_ta
FOREIGN KEY (companyID) REFERENCES company(companyID);
^^^^^^^^^^^^^ ^
Servus
Manfred
Karen & Manfred, I can get this to work if I set it up when I create new
tables, but I need to change an existing database and it doesn't work
(perhaps a BUG?). When I try the following with my current database I
get an error:
giibdb=# ALTER TABLE project ADD CONSTRAINT company_is_ta FOREIGN
KEY (companyID) REFERENCES tblCompanies(companyID);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN
KEY check(s)
ERROR: company_is_ta referential integrity violation - key
referenced from project not found in company
Is there a way I can modify an existing database to get the same
end-result (eg it works when DB is set up, before it is populated with
data)?
Ron
Karen Grose wrote:
> Ron,
> I have done this by adding the attribute to the table with nulls allowed and adding a constraint to the table for the
foreignkey... works like a charm:
>
> CREATE TABLE TESTTYPE (
> TESTTYPEKEY char(30) NOT NULL,
> TESTTYPENAME varchar(255) NULL,
> TESTTYPEDESC varchar(255) NULL,
> TESTTYPELABEL varchar(255) NULL,
> CONSTRAINT XPKTESTTYPE
> PRIMARY KEY (TESTTYPEKEY)
> )
> ;
>
> CREATE TABLE TEST (
> TESTKEY char(30) NOT NULL,
> TESTTYPEKEY char(30) NULL,
> CONSTRAINT LOG_PK
> PRIMARY KEY (TEST_PK),
> CONSTRAINT testtype_test
> FOREIGN KEY (TESTTYPEKEY)
> REFERENCES TESTTYPE
> )
> ;
> Karen L. Grose
> Vigilos Inc.
>
> Karen L. Grose
> Vigilos Inc.
> 2030 First Avenue
> Suite 300
> Seattle, WA 98121
> 206.728.6464 ext. 111 :Phone
> 206.728.6440 :Fax
> 206.335-8386 :Cell
>
>
>
> -----Original Message-----
> From: Ron [mailto:rstpATlin@uxwav.esDOTcom]
> Sent: Thursday, October 23, 2003 9:02 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Nullable 'Foreign Key-like' Constraint
>
>
> I posted this to 'questions' yesterday instead of 'general' by mistake.
> Sorry if anyone received duplicates.
> ----------------------------------------------------
>
> Mandatories: Ver 7.3.4, Redhat Linux 8.0, P4, 2GB RAM
>
> I want to add a 'nullable' foreign key to a column in a table. I have
> tables "company" and "project" which may be related by
> company.companyID <-> project.companyID.
> project.companyID is allowed to be null. However, when someone tries to
> delete a company which is still referenced in "project" I want a
> constraint restricting deletion.
>
> I tried:
> ALTER TABLE company ADD CONSTRAINT company_is_ta
> CHECK (companyID IN
> (SELECT companyID FROM project));
> and I receive:
> ERROR: cannot use subselect in CHECK constraint expression
>
>
> Then I came across this previous post which showed how to set it up when
> the table is created. I tried it and it works for a new table, but I
> can't get it to work with existing tables.
>
> 1) My attempt:
> ALTER TABLE project ALTER COLUMN companyID SET DEFAULT NULL;
> ALTER TABLE project ADD CONSTRAINT company_is_ta companyID
> REFERENCES company(companyID);
> (plus variations on the above, resulting in errors, all similar to:)
> ERROR: parser: parse error at or near "companyID" at character 53
>
> 2) based on this previous posting:
>
> > From: Manfred Koizar (mkoi-pg@aon.at)
> > Subject: Re: NULL Foreign Key
> > Newsgroups:comp.databases.postgresql.general,
> > comp.databases.postgresql.questions
> > Date: 2002-07-17 05:51:19 PST
>
> > On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
> > <KuhnDK@navair.navy.mil> wrote:
> > >Can I make a foreign key that is allowed to be NULL?
>
> > Yes:
>
> > fred=# CREATE TABLE father (i INT PRIMARY KEY);
> > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> > 'father_pkey' for table 'father'
> > CREATE
> > fred=# CREATE TABLE son (i INT REFERENCES father);
> > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> > check(s)
> > CREATE
> > fred=# INSERT INTO father VALUES (1);
> > INSERT 183317 1
> > fred=# INSERT INTO son VALUES (1);
> > INSERT 183318 1
> > fred=# INSERT INTO son VALUES (2);
> > ERROR: <unnamed> referential integrity violation - key referenced
> > from son not found in father
> > fred=# INSERT INTO son VALUES (NULL);
> > INSERT 183320 1
>
> > Servus
> > Manfred
>
> Anyone know how I can get this to work? BTW I don't want to use 'ignore'
> rules when someone attempts to delete the company as I want the
> constraint message to be shown in the app's browser.
>
> TIA
> Ron
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
On Fri, 24 Oct 2003 18:19:05 GMT, Ron <rstpATlin@uxwav.esDOTcom>
wrote:
>When I try the following with my current database I
>get an error:
> giibdb=# ALTER TABLE project ADD CONSTRAINT company_is_ta FOREIGN
> KEY (companyID) REFERENCES tblCompanies(companyID);
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN
> KEY check(s)
> ERROR: company_is_ta referential integrity violation - key
> referenced from project not found in company
>
>Is there a way I can modify an existing database to get the same
>end-result (eg it works when DB is set up, before it is populated with
>data)?
Ron, you can have referential integrity or you can have projects
referencing nonexistent companies, but not both. Whichever you
implement first prevents creation of the other one.
CREATE TABLE company (
companyId int PRIMARY KEY,
name text
);
INSERT INTO company VALUES (1, 'one');
INSERT INTO company VALUES (2, 'two');
CREATE TABLE project (
projectId int PRIMARY KEY,
name text,
companyId int
);
INSERT INTO project VALUES (1, 'p1c1', 1);
INSERT INTO project VALUES (2, 'p2c1', 1);
INSERT INTO project VALUES (3, 'p3', NULL);
-- this works:
ALTER TABLE project ADD CONSTRAINT company_is_ta
FOREIGN KEY (companyId) REFERENCES company (companyId);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
-- this will fail:
INSERT INTO project VALUES (4, 'p4c7', 7);
ERROR: company_is_ta referential integrity violation -
key referenced from project not found in company
-- now the other way round:
ALTER TABLE project DROP CONSTRAINT company_is_ta;
INSERT INTO project VALUES (4, 'p4c7', 7);
ALTER TABLE project ADD CONSTRAINT company_is_ta
FOREIGN KEY (companyId) REFERENCES company (companyId);
ERROR: company_is_ta referential integrity violation -
key referenced from project not found in company
To find projects violating the constraint:
SELECT * FROM project AS p WHERE NOT companyId IS NULL
AND NOT EXISTS (
SELECT * FROM company AS c WHERE c.companyId = p.companyId);
Servus
Manfred