NULL Foreign Keys

[Aidy]

I have a table, the entry of which may or not relate to a row in another
table. Is it ok to make that field a nullable foreign key?

Example;

Car_ID, Car
1, Ford
2, Nissan

User_ID, Car_ID
1, 2
2, NULL
3, 1

So user 1 has a Nissan, but user 2 has no car at all. I know an alternative
is to have a seperate linking table that dictates who has a car, and I know
that approach has its pros and cons - but basically, for the sake of
simplicity in data representation, joining in queries, number of tables to
manage etc is it accepted as "ok" to have nullable FKs? I've found a few
articles that pretty much sum up what I feel the answer to be - ie use the
right tools for the job at hand, and also some that say "yes it's fine" and
some folk that so "no it's horrid".

Just wondered if people had thoughts on this.

[Jack Vamvas]

This is quite a common scenario ; for example in a Parent - Child scenario ,
you enter the child data without knowing what the Parent data is at present.
This is usually a reflection of bad data. The best thing to do is to add a
CONSTRINT that checks and handles this type of data.

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com

"Aidy" <ai...@xxnoemailxx.com> wrote in message
news:99SdnW4Le-s...@bt.com...

[Aidy]

> you enter the child data without knowing what the Parent data is at
> present

It's not that, it's more like a "may have" relationship. Ie a user "may
have" a car, but if they don't their Car_ID is NULL. Obviously the User
table contains more info, not just the Car_ID.

[David Portas]

"Aidy" <ai...@xxnoemailxx.com> wrote in message

news:_JadnVsW4O1...@bt.com...

Assuming User_ID is the primary key of User, you don't need a nullable
foreign key at all:

CREATE TABLE User
(User_ID INT NOT NULL PRIMARY KEY,
/* ... other columns */);

CREATE TABLE Car
(Car_ID INT NOT NULL PRIMARY KEY,
/* ... other columns */);

CREATE TABLE UserCar
(User_ID INT NOT NULL PRIMARY KEY,
Car_ID INT NOT NULL REFERENCES Car(Car_ID));

This is called non-loss decomposition and it results in a schema in 5th
Normal Form, something that ought to be your goal when designing any SQL
database.

--
David Portas

[David Portas]

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:TuWdnRkMHuRBNzva...@giganews.com...

>
> CREATE TABLE UserCar
> (User_ID INT NOT NULL PRIMARY KEY,
> Car_ID INT NOT NULL REFERENCES Car(Car_ID));
>

Oops. I missed the other FK:

CREATE TABLE UserCar
(User_ID INT NOT NULL PRIMARY KEY REFERENCES User (User_ID),

Car_ID INT NOT NULL REFERENCES Car(Car_ID));

--
David Portas

[Aidy]

Yeah I get that, but it requires the addition of an extra table.

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:TuWdnRkMHuRBNzva...@giganews.com...

[Ed Murphy]

Aidy wrote:

> Yeah I get that, but it requires the addition of an extra table.

Let me show you this SYCDEFIL table that I've been working with
lately. Extra tables are not always a bad thing.

[David Portas]

"Aidy" <ai...@xxnoemailxx.com> wrote in message

news:B4SdnXstl8Qpejva...@bt.com...

> Yeah I get that, but it requires the addition of an extra table.
>

And why would that be a problem?

--
David Portas

[Aidy]

Usually more admin required (CMS I mean) mainly.

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message

news:PfydnZolv9gl8jra...@giganews.com...

[Jack Vamvas]

In a CMS scenario , the only things you would need to change (once you've
set up the tables) is your sql statements

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com

"Aidy" <ai...@xxnoemailxx.com> wrote in message

news:GIidnWkuUKUBtzXa...@bt.com...