Dan Waters
dwaters at usinternet.com
Mon Mar 16 14:17:53 CDT 2009
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