September 22, 2014 at 8:03 am
Hello mates!, Grab your coffee and check this out...
In this time i would like to ask for your advice to solve this problem...
I was trying to build a foreign key from Table1(Field) to Table2(Field). Field from table1 is primary key so when i run this in SQL
Alter Table Table2 Add Constraint Fk_Table1_Table2
Foreign Key (Field) References Table1 (Field)
It returns
Mens. 547, Nivel 16, Estado 0, Línea 1
Instrucción ALTER TABLE en conflicto con la restricción FOREIGN KEY "Fk_Table1_Table2". El conflicto ha aparecido en la base de datos "Database", tabla "dbo.Table1", column 'Field'.
Of course it happens because there exists some records into Table2 which Field(Table2) doesn't match with Field(Table1) so i ask which of the follow query is better to identify those records...
Select * From Table2 Where Field Not In (select Field From Table1)
Select * From Table2 Left Join Table1 On Table1.Field=Table2.Field Where Table1.Field Is Null
If both are usefull anyway, which might be better??
PD. I attached the SQL Code to check this case.
Regards
September 22, 2014 at 8:08 am
Either one will do, if it's a once-off (which it should be), it really doesn't matter which you use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2014 at 8:13 am
ahh by the way, someone told me that first query might return unexpected results, ¿how could this be possible?
September 22, 2014 at 8:24 am
If Field in Table1 is ever null, that query would return no rows. However since that's the primary key, it's not nullable and hence you don't have that concern
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2014 at 10:24 am
GilaMonster (9/22/2014)
If Field in Table1 is ever null, that query would return no rows. However since that's the primary key (I assume, since it's the reference for a fk), it's not nullable and hence you don't have that concern
If doesn't have to be a PK, just a unique index. Since a unique index allows a single NULL value, the subquery should still include a WHERE condition to exclude NULL.
Edit: Like this:
Select * From Table2 Where Field Not In (select Field From Table1 where Field is not NULL)
Any time you use "NOT IN" you should insure no NULLs appear in the value list.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 22, 2014 at 12:59 pm
In this case no assumption was needed, since the OP said
Field from table1 is primary key
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2014 at 1:25 pm
GilaMonster (9/22/2014)
In this case no assumption was needed, since the OP saidField from table1 is primary key
It's currently the PK (perhaps). Just in case, I'd write the code so that NULLs can never cause a bad query result in the future.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 23, 2014 at 6:46 am
You can avoid the whole question of NULLs with NOT EXISTS. Also helps you to not abuse a LEFT JOIN.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply