Ahh. Should have been: WHERE A <= 10 AND A >= 10
Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected] [mailto:[email protected]] On Behalf Of Greg Low (??????) Sent: Tuesday, 9 February 2016 4:16 PM To: ozDotNet <[email protected]> Subject: RE: SQL foreign key question SQL Server also pre-processes all sorts of things. For example: WHERE A <= 10 AND >= 10 That can safely be replaced by: WHERE A = 10 But they are very cautious about rewriting your code. Very easy to break it. But they really have to. While humans can write lousy code, code generators are spectacular at doing so. Here’s an example: WHERE b.entry >= ISNULL(NULL,'1/1/1900') AND b.entry < DATEADD(DAY,1,ISNULL(NULL,'1/1/3000') ) AND --Filter on Facility ( (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR (NULL IS NULL) ) AND --Filter on Company ( (NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR (NULL IS NULL) ) AND --Filter on Financial Class ( (NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR (NULL IS NULL) ) Etc. etc. etc. (Not to mention their Y3K problem) Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Greg Low (??????) Sent: Tuesday, 9 February 2016 4:12 PM To: ozDotNet <[email protected]<mailto:[email protected]>> Subject: RE: SQL foreign key question Yes, if it just points to itself, it already knows that it satisfies it. It is respecting the constraint. There is nothing to check. I see dumb stuff like this all the time. Was at a site recently where they had triple-created all the FK constraints. No big surprise that it doesn’t actually check the value 3 times (to be sure, to be sure, to be sure), regardless of what it shows in the plan. It’s also one of the reasons why I always name constraints. Then you can’t accidentally multiple-create them. (Reason #3723924 for not using visual table designers). Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of David Burstin Sent: Tuesday, 9 February 2016 4:06 PM To: ozDotNet <[email protected]<mailto:[email protected]>> Subject: Re: SQL foreign key question On 9 February 2016 at 16:02, Greg Low (罗格雷格博士) <[email protected]<mailto:[email protected]>> wrote: You often see tables referring to themselves (due to some sort of hierarchical data) but having a column refer to itself seems at best pointless, at worst silly. For me the bigger question is how this actually works. Does the sql engine just decide to ignore the constraint because it is so obviously dumb? If so, am I the only one bothered by that? I would have actually preferred if no records could be added to the table, because at least then I know that the engine is respecting my constraints. And reason #3723923 why I won’t ever use such a visual table designer. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410<tel:%2B61%20419201410> mobile│ +61 3 8676 4913<tel:%2B61%203%208676%204913> fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Tony McGee Sent: Tuesday, 9 February 2016 3:59 PM To: ozDotNet <[email protected]<mailto:[email protected]>> Subject: Re: SQL foreign key question This can sometimes happen to a primary key if you use the visual query designer to create a FK relationship and don't change the defaults before clicking OK. It looks like it's ignored in the insert query execution plan. On 9 Feb 2016 2:32 pm, "David Burstin" <[email protected]<mailto:[email protected]>> wrote: I came across this (snipped to protect the innocent): CREATE TABLE [dbo].[V2_BREC_NMIStatusHistory] ( [NMIStatusHistoryId] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL, <snip/> CONSTRAINT [PK_V2_BREC_NMIStatusHistory] PRIMARY KEY CLUSTERED ([NMIStatusHistoryId] ASC), CONSTRAINT [FK_V2_BREC_NMIStatusHistory_V2_BREC_NMIStatusHistory] FOREIGN KEY ([NMIStatusHistoryId]) REFERENCES [dbo].[V2_BREC_NMIStatusHistory] ([NMIStatusHistoryId]) ); Notice that the primary key identity field has a foreign key constraint on itself. How does this work? I would have thought that any attempt to add a record would check the table for the existence of the new key, and as it obviously wouldn’t exist yet, that would break the foreign key constraint resulting in the record not being written. But, the table has plenty of data. Anyone have any ideas how this actually works, or does it just do nothing?
