Asger Blond
ab-mi at post3.tele.dk
Mon Mar 16 13:40:49 CDT 2009
>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