473,394 Members | 1,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Nullable 'Foreign Key-like' Constraint

Ron
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 (mk*****@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)"
<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'
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

Jul 19 '05 #1
0 4484

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

Similar topics

5
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 foreign key whithout index. Best regards ...
20
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 parent. It is implemented as a child record has FK...
26
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 is related to both BidItem and BidAddendum, based...
7
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 ISNULL'ed fields as an additional column, though...
3
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 create table '.\sampleDB\student_module.frm' (errno:...
1
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 changed. For example, let's say I have a...
9
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 secondary key over a primary one. I have a lot of...
10
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 foreign keys are locked (or evaluated) in for a...
9
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 we take up various comma separated data files for...
6
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 not been able to do this elegantly. When I first...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.