Error saving foreign key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error saving foreign key

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-05-13 : 10:17:30

hi,

I have two tables, users and transactions. when i try to add the foreign key and save i get an error.

i am trying to see if there are any user ids in the transaction table, that are not in the user table.

i tried the following:

SELECT tblUser.id
FROM tblUser
WHERE NOT EXIST (SELECT user_id
FROM tblTransaction
WHERE tblUser.id = tblTransaction.user_id

but i'm getting errors, am i doing it wrong?

please help. could there be another reason i can't add a foreign key? To my best understanding, the foreign key (user_id) has to match an id from the user table, but the id from the user table (primary key), does not have to be in the transactions table, right?

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:19:04

SELECT tblUser.id
FROM tblUser as t
WHERE NOT EXIST (SELECT user_id
FROM tblTransaction
WHERE t.id = user_id)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:21:56

can you post error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-05-13 : 10:23:15

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

same errors

am i missing something?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:24:26

the posted query is missing a end braces

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:25:55

quote:
Originally posted by xrum

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

same errors

am i missing something?

See my first reply

Madhivanan

Failing to plan is Planning to fail

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-05-13 : 10:26:00

thanks,but that's the error i'm getting using this one:

SELECT tblUser.id
FROM tblUser as t
WHERE NOT EXIST (SELECT user_id
FROM tblTransaction
WHERE t.id = user_id)

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 10:28:07

WHERE NOT EXISTS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:29:33

quote:
Originally posted by xrum

thanks,but that's the error i'm getting using this one:

SELECT tblUser.id
FROM tblUser as t
WHERE NOT EXIST (SELECT user_id
FROM tblTransaction
WHERE t.id = user_id)

SELECT tblUser.id
FROM tblUser as t
WHERE NOT EXISTS (SELECT user_id
FROM tblTransaction
WHERE t.id = user_id)

EXIST should be EXISTS

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:29:39

quote:
Originally posted by xrum

thanks,but that's the error i'm getting using this one:

SELECT tblUser.id
FROM tblUser as t
WHERE NOT EXIST (SELECT user_id
FROM tblTransaction
WHERE t.id = user_id)

there's a typo
its EXISTS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:29:58

ah double

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-05-13 : 10:47:39

now i'm getting this:

The multi-part identifier "tblUser.id" could not be bound.

:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:49:42

[code]SELECT t.id
FROM tblUser as t
WHERE NOT EXISTS (SELECT 1
FROM tblTransaction
WHERE t.id = user_id)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-05-13 : 10:53:40

this worked, thanks for your help

SELECT USER_ID FROM tblTransaction WHERE USER_ID NOT IN (SELECT ID FROM TBLUSER)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:57:06

quote:
Originally posted by xrum

this worked, thanks for your help

SELECT USER_ID FROM tblTransaction WHERE USER_ID NOT IN (SELECT ID FROM TBLUSER)

have you tested performance of both? I have seen cases where NOT EXISTS perform better

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:59:14

quote:
Originally posted by xrum

now i'm getting this:

The multi-part identifier "tblUser.id" could not be bound.

:(

Why didn't you use the exact code posted?
You must have modified it and ran

Have you run this?

SELECT t.id
FROM tblUser as t
WHERE NOT EXISTS (SELECT 1
FROM tblTransaction
WHERE t.id = user_id)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources