Nullable Foreign key constriants [Programming SQL]

Prev: Can SQL Server be run locally?
Next: select based on count

From: David Portas on 14 Dec 2009 05:56

On 14 Dec, 02:23, "Jay" <s...(a)nospam.org> wrote:
> "David Portas" <REMOVE_BEFORE_REPLYING_dpor...(a)acm.org> wrote in message
>
> news:8t2dnXp07vjCrbjWnZ2dnUVZ8gqdnZ2d(a)giganews.com...
> Users and developers may automatically assume they can join on foreign
>
> > key columns and get a result that includes every row in the joined tables.
>
> Uh, by definition this would not be true. If you are joining on the foreign
> key column, it would be for the specific data available in the lookup table
> for that ONE row. After all, if it's a FK, then it's a PK in the other
> table.
>

The assumption I was referring to is the one that people often make
when they join a table with a foreign key to its parent: that every
row in the referencing table will join to exactly one row in the
parent. That is true in the case of non-nullable columns but not if
the foreign key constraint is nullable. Nullable foreign keys do not
enforce the constraint that most people understand a foreign key to
be.

--
David Portas

From: gareth erskine-jones on 14 Dec 2009 10:21

From: Jay on 14 Dec 2009 14:15

I don't think that's right, but am more than willing to be proven wrong.
Here is a small stub, please modify it to show what you're talking about.

use tempdb;

if object_id(N'dbo.try', N'U') is not null

drop table dbo.try;

if object_id(N'dbo.lookup', N'U') is not null

drop table dbo.lookup;

create table lookup (

lookup_key int primary key,

value varchar(20)

);

create table try (

try_key int primary key,

try_val varchar(20),

lookup_key int null,

foreign key (lookup_key) references lookup

);

insert into lookup values(1, 'one');

insert into lookup values(2, 'two');

insert into lookup values(3, 'three');

insert into lookup values(4, 'four');

insert into try values(1, 'first', 1);

select *

from try

inner join lookup on try.lookup_key = lookup.lookup_key;

if object_id(N'dbo.try', N'U') is not null

drop table dbo.try;

if object_id(N'dbo.lookup', N'U') is not null

drop table dbo.lookup;

"David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message
news:ab1356fe-41a9-44a6-93ec-c1c16d4f1169(a)y24g2000yqb.googlegroups.com...
On 14 Dec, 02:23, "Jay" <s...(a)nospam.org> wrote:
> "David Portas" <REMOVE_BEFORE_REPLYING_dpor...(a)acm.org> wrote in message
>
> news:8t2dnXp07vjCrbjWnZ2dnUVZ8gqdnZ2d(a)giganews.com...
> Users and developers may automatically assume they can join on foreign
>
> > key columns and get a result that includes every row in the joined
> > tables.
>
> Uh, by definition this would not be true. If you are joining on the
> foreign
> key column, it would be for the specific data available in the lookup
> table
> for that ONE row. After all, if it's a FK, then it's a PK in the other
> table.
>

The assumption I was referring to is the one that people often make
when they join a table with a foreign key to its parent: that every
row in the referencing table will join to exactly one row in the
parent. That is true in the case of non-nullable columns but not if
the foreign key constraint is nullable. Nullable foreign keys do not
enforce the constraint that most people understand a foreign key to
be.

--
David Portas

From: Sylvain Lafontaine on 14 Dec 2009 14:34

On Mon, 14 Dec 2009 02:56:16 -0800 (PST), David Portas
<REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote:

>On 14 Dec, 02:23, "Jay" <s...(a)nospam.org> wrote:
>> "David Portas" <REMOVE_BEFORE_REPLYING_dpor...(a)acm.org> wrote in message
>>
>> news:8t2dnXp07vjCrbjWnZ2dnUVZ8gqdnZ2d(a)giganews.com...
>> Users and developers may automatically assume they can join on foreign
>>
>> > key columns and get a result that includes every row in the joined
>> > tables.
>>
>> Uh, by definition this would not be true. If you are joining on the
>> foreign
>> key column, it would be for the specific data available in the lookup
>> table
>> for that ONE row. After all, if it's a FK, then it's a PK in the other
>> table.
>>
>
>The assumption I was referring to is the one that people often make
>when they join a table with a foreign key to its parent: that every
>row in the referencing table will join to exactly one row in the
>parent. That is true in the case of non-nullable columns but not if
>the foreign key constraint is nullable. Nullable foreign keys do not
>enforce the constraint that most people understand a foreign key to

Very strange assumption; only a newbie could possibly think like that and
even then, using a Not Nullable Foreign Key is not a solution but add to the
problem instead by giving him the impression that there is never a missing
data for this particular piece of information in the foreign table.

Futhermore, he shouldn't have any concern for any row that he doesn't want
to retrieve excerpt in the situation where he need to retrieve *all* the
rows in the foreign table for whatever reason but then, when you make a
join, it's probably because you also need data from the primary data. With
a Not Nullable Foreign Key that is related to a bogus data - or magic key,
this would have the effect of including bogus data into the result. If this
is the case, this primary table must then first be stripped of this bogus
data and then the Inner Join replace with a Left Join. You end up with the
same result at the end, including Null values but now, with the extra step
of first stripping the bogus data from the primay table in the Select query.

Instead of making it simpler for him, you get exactly the opposite: the true
nature of the relationship is buried under a cover of bogus data and other
magic keys and the final query is more complex to read and write; all this
to get the exact same result at the end. I don't see what's the advantage
here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

From: David Portas on 14 Dec 2009 15:04

"Jay" <spam(a)nospam.org> wrote in message
news:%23BP4THPfKHA.2780(a)TK2MSFTNGP05.phx.gbl...
>I don't think that's right, but am more than willing to be proven wrong.
>Here is a small stub, please modify it to show what you're talking about.
>

I don't see why it needs modifying. Your example has every row in the
referencing table ("try") joining to exactly one row in the parent
("lookup") - which is what I said.

--
David Portas

First | Prev | Next | Last
Pages: 1 2 3 4 5 6 7 8
Prev: Can SQL Server be run locally?
Next: select based on count