The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Option Foreign Key Enforcement

Is there a database that can do "optional" foreign keys?  Meaning FKs that are only enforced for non-null values?

Here's an example --

Foreign keys are simple.  The value in a field in Table A must match a key in Table B.  It *has* to match, there's no option. But what if you don't have a related record in Table B?

Say you have a table for Companies, and a table for People.  The "company_id" value for a record in People *has* to match an "id" value for a record in Companies.

But what if I have an unemployed person with no company?  Then I have to have a fake company in the Companies table (called "No Company," perhaps) so I can enter the person, because they *have* to have a matching record.  If I want to be able to enter the person anyway, then I can't have referential integrity.

So, how about this: have a way to enforce referential integrity only on non-null values.  Which means if the "company_id" field for a record in People record is *not null*, then there better be a matching record in Companies.  But if it's null, then referential integrity is ignored.

Does any database platform do this?  Is it a stupid idea?  I thought this up years ago, but have never found it addressed anywhere.
Deane Send private email
Saturday, February 18, 2006
 
 
Oracle does this just fine.  I've been doing that for years, and no, it's not a stupid idea ;-)
Ryan Send private email
Saturday, February 18, 2006
 
 
SQL Server works this way too.
Raj Chaudhuri Send private email
Saturday, February 18, 2006
 
 
>SQL Server works this way too.

How?  Is there a setting?  A constraint?  Does it do it out of the box?

How could I have missed this?
Deane Send private email
Saturday, February 18, 2006
 
 
Is there a database that *doesn't* work like this?
It is not a stupid idea at all. If you don't want null on the FK column, you can set it as NOT NULL, but the fact that a column is a FK shouldn't have anything to do with the nulls. Now the question arises: what means a null on a FK column? Well, it's very easy. In general a null means an *undefined* value - it *could be* 1, 2 or 7, but you don't know how much it is. Now if you don't know the value what should you do with the FK? Simple: forget about it. Don't bother checking it. Because you will check it when you'll know the value!
smalltalk Send private email
Sunday, February 19, 2006
 
 
yes, this kind of behaviour is implemented in very relational database.
In case if your FK constraint is one-column foreign key then FK then in case if the refering column contains a null value,  then the FK is satisfied.
In case if you do not want to allow to insert null values, you have to explictly declare NOT NULL restriction.

It is a little bit more  sophisticated in case of composite foreigh keys.
Assume that you have
a table
employees  (department, emp_id, name)
PRIMARY KEY is department and emp_id
(employees are given numbers which are unique at the department level)
and
salary (department, emp_id, month, sum)

A composite foreign key is defined
s1alary(department, emp_id) references to employees(department, emp_id)

assume that department and emp_id collumns in salary are allowed to have null values
assume there is one record in employees table
    sales, 15, John Smit
are we allowed to insert
  sales, 15, July, 10000
to salary?
YES
What is about
  sales, null, July, 10000?
YES

But what about
  marketing, null, July, 10000?

The answer is YES
According to ANSI SQL the DEFAULT behaviour is
in case if at least one column of a composite foreign key has a null value, then foreign key constrain is satisfied even in case if other columns does NOT match

All relational DBMS  implement this behaviour. Check your Oracle

Actually ANSI SQL specified a few levels of behaviour for composite FK in case of null values. but DBMS does not support all ANSI level, only default one is supported
Andrei Lopatenko Send private email
Sunday, February 19, 2006
 
 
Oh, Good Lord, are you all serious?

I once had a SQL Server DBA tell me this was NOT possible, and, since then, I always took that as gospel.  He told I'd have to write some big trigger, function, constraint thing to make it work.

What a bunch of crap that turned out to be.

Thanks, everyone.
Deane Send private email
Sunday, February 19, 2006
 
 
It depends on what sort of relation you're dealing with.  If you're dealing with a one-to-many relation, you can certainly have a null foreign key on the many side.  This is known as an "orphaned" record.

Whether it's a good idea to have orphaned records depends entirely on the context; in terms of the real-world item they represent, does it make sense to have them without the parent?  You also need to ask yourself how you'll display them and edit them, for instance, and how you'll associate them with a parent record later if you need to.
Kyralessa Send private email
Sunday, February 19, 2006
 
 
The only out of the ordinary thing I had to deal with using MS Sql Server is you cannot put a unique constraint on a column if you are expecting more than one null.

There is a workaround however.
Ben Dempsey Send private email
Sunday, February 19, 2006
 
 
Deane

it is possible,
you can check it in 1 minute
Run this script

create table emp(
emp_id integer primary key,
name varchar(20));

create table salary(
emp_id integer references emp(emp_id),
month integer,
salary integer);

insert into emp values (1, 'John Smith');
insert into salary (null, 5, 10000);


if you get error message, then your DBA is right
if you do not get, then
Andrei Lopatenko Send private email
Monday, February 20, 2006
 
 
sorry, mistypes in the prvious message
Try this

<i>
create table emp(
emp_id integer primary key,
name varchar(20));

create table salary(
emp_id integer references emp(emp_id),
month integer,
salary integer);

insert into emp values (1, 'John Smith');
insert into salary values(null, 5, 10000);
</i>
Andrei Lopatenko Send private email
Monday, February 20, 2006
 
 
"I once had a SQL Server DBA tell me this was NOT possible"

Rule #1, don't trust DBAs!
Almost H. Anonymous Send private email
Monday, February 20, 2006
 
 
Sorry Deane but it sounds like your DBA was being overly cautious (or I try never to eliminate the BDFH option too :) )

That said, Andrei makes an excellent point and one your DBA may have been attempting to deal with in a generic "can't do that" statement.  Many systems contain FK tables of composite keys. 

Now is it a bug or a feature?  Well that depends on whether you need it right?  I also like Andrei's quick test, it will tell you whether the exact implementation you have will deal with it as expect.  Those snippets are always handy to have around.
MSHack
Monday, February 20, 2006
 
 
Good grief, did I a lost in translation moment.  And now for a comprehensible version of my last sentence:

"I also like Andrei's quick test, as it will tell you whether your implementation will deal with the keys as expected.  These snippets are always handy to have around."
MSHack
Monday, February 20, 2006
 
 
Go ahead - mock me.  I deserve it.
MSHack
Monday, February 20, 2006
 
 
>> Rule #1, don't trust DBAs!

Rule #1: Don't trust anyone. ALWAYS TEST YOUR ASSUMPTIONS
David Aldridge Send private email
Monday, February 20, 2006
 
 
<<Is there a database that *doesn't* work like this?>>

Access97

Welcome to my world of programming circles around Access97 limitations....
Fritz Huber Send private email
Monday, February 20, 2006
 
 
David Aldridge
ALWAYS TEST YOUR ASSUMPTIONS

One time I implemented the dupes elimination aqlgorithms for a particular data structure differently then it was recomended.
My implememntation was around 50 times faster for 1 million elements data structure then implemented according to recommended algorithm.
I was quite sure that recommended was right. But I decided to test "what if ....I do it otherwise"

Or I was quite sure that Straussen algorithm is faster then "naive" for n = 100 matrixes. Just for curiosity I decided to check it and read some literature.
I was no right
Straussen vv. Naive depends of architecture, implementation and in some case nasive outperforms Straussen up to n = 128 matrixes
Andrei Lopatenko Send private email
Tuesday, February 21, 2006
 
 
Sure it's possible. But crap,

Do it properly and use a junction table. Then nobody's left wondering what the null value 'means'.

In fact let's all have that mind numbingly tedious discussion all over again, the meaning of null.

Monday, February 27, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz