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 foreign key... 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:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 9:02 AM To: [EMAIL PROTECTED] 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 ([EMAIL PROTECTED]) > 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)" > <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
