472,353 Members | 1,438 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Nullable 'Foreign Key-like' Constraint

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 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 (mk*****@aon.at)
Subject: Re: NULL Foreign Key
Date: 2002-07-17 05:51:19 PST On Tue, 16 Jul 2002 17:10:32 -0700, "Kuhn, Dylan K (NDTI)"
<Ku****@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'
fred=# CREATE TABLE son (i INT REFERENCES father);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
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
INSERT 183320 1 Servus

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.


Jul 19 '05 #1
0 4409

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Olivier Crèvecoeur | last post by:
Hello, Excuse me for my poor english. I would kike know if create index on the foreign key it's necessary or if Oracle, are optimized for using...
by: Andy | last post by:
Hi All! General statement: FK should not be nullabe to avoid orphans in DB. Real life: Business rule says that not every record will have a...
by: pb648174 | last post by:
I have a table called BidItem which has another table called BidAddendum related to it by foreign key. I have another table called BidFolder which...
by: Clifford Heath | last post by:
I have a case where a table has two candidate primary keys, but either (but not both) may be NULL. I don't want to store a copy of the concatenated...
by: heroe | last post by:
Hello *, i write you with an urgent problem. I would like to create forign keys to datetime field, but it doesn't seem to work. I get Can't...
by: Good Man | last post by:
Hi there I was just wondering why I should explicitly create a foreign key, especially if i'm not concerned with Cascading/Deleting when a row is...
by: Jax | last post by:
I'm making my first ever database for my program. I understand the concept of one to many relationships but fail to see the advantage of using a...
by: Shawn Chisholm | last post by:
Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the...
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short...
by: JDS | last post by:
I want to be able to use effectively a table adaptor query that can take several arguments with several of those arguments possibly null. I have...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.