[dba-SQLServer] NULLs

Dan Waters dwaters at usinternet.com
Mon Mar 16 16:31:28 CDT 2009


Hi Asger,

I do appreciate you pointing this out - might save myself a major problem
someday!

Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Monday, March 16, 2009 4:07 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] NULLs

Hi Dan,
Referential integrity says: "You can't have a value in a foreign key which
isn't present in the primary key". But you can have a NULL in the foreign
key. Why? Because NULL isn't a value.

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Dan Waters
Sendt: 16. marts 2009 20:18
Til: 'Discussion concerning MS SQL Server'
Emne: Re: [dba-SQLServer] NULLs

Hi Asger,

I stand corrected!  I just tried this with two simple tables and a
relationship that did enforce referential integrity, and did have cascade
update and delete checked.

While I can't create a new record in the subtable that doesn't have a
matching number in it's FK field, I could delete the number in the FK field
after the record was initially saved.

I didn't know this could be done - even though referential integrity was
checked, I could easily defeat it!  Dangerous!

Thanks!
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Monday, March 16, 2009 1:41 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] NULLs

>The condition is that the table relationship was created so that
>Referential integrity was NOT enforced.  If referential integrity is
>enforced, the database will not allow the creation of a record in a sub 
>table without a corresponding record in the primary table.

Dan, you are not right. Enforcing referential integrity (= making a FK
constraint) does NOT disallow NULLs in the referencing column (the FK
column) of the sub table.
This is a common misconception to which I pointed in previous postings. If
you want to disallow NULLs in the FK you have to make a NOT NULL constraint
on the FK.

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Dan Waters
Sendt: 16. marts 2009 19:22
Til: 'Discussion concerning MS SQL Server'
Emne: Re: [dba-SQLServer] NULLs

Good Question!

Let's say you have a primary table and a sub table connected by a one to
many relationship.  Normally, the FK in the sub table is equal to the PK in
the main table for at least one record.

But, let say the developer has created a form which is bound only to the sub
table.  In this case, under one condition, that form could add a record to
the sub table with no corresponding record in the primary table.  The
condition is that the table relationship was created so that referential
integrity was NOT enforced.  If referential integrity is enforced, the
database will not allow the creation of a record in a sub table without a
corresponding record in the primary table.

When a developer creates a relationship between two tables in the
Relationships window, a dialog box appears with a checkbox titled 'Enforce
Referential Integrity'.  It's unchecked by default.

Normally, a developer does want referential integrity enforced, but it is
his/her choice.  I've been developing for many years, but can't think of a
time when I did this - but I'm sure someone here can give a good example.

Hope this helps!
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Monday, March 16, 2009 12:52 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] NULLs

I'm more than confused and Gustav, if you can admit it, I can too!

A FK is just a primary key in a related table, so how can it be null to 
begin with? I can see the record not having a match in the related table... 
if that's what you mean.

Susan H.


> Hi Dan
>
> I think you - or someone else? - are contradicting yourself or someone 
> else(?) - at least "me am totalyful confusticated" now!

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com






More information about the dba-SQLServer mailing list