1

I have table calls 'Users' and there is UserID there.

I have also table calls UsersFriends that look like:

create table UsersFriends
(   
UserID int references Users(UserID),
FriendID int references Users(UserID), 
primary key(UserID,FriendID)
)

As you see, UserID and FriendID are references from Users(UserID).

I want to make sure that there is no enrty like: (1,1) because can't be friend of himself. So, I tried to make Check but isn't.. I tried to do like this:

create table UsersFriends
(   
User1ID int references Users(UserID),
FriendID int references Users(UserID) CHECK (FriendID in (select u.UserID from Users u where      
u.UserID!= User1ID)),
primary key(User1ID,FriendID)
)

But I've got error:

Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Someone can help me please?

Thanks.

Zvi
  • 577
  • 6
  • 19
  • I removed the mysql tag because the syntax is only SQL Server and MySQL doesn't support check constraints anyway –  May 09 '14 at 12:23

1 Answers1

2

You are defining the check constraint inline and moreover subqueries are not allowed. You need to define the constraint at table level like below

create table UsersFriends
(   
UserID int references Users(UserID),
FriendID int references Users(UserID), 
primary key(UserID,FriendID),
CONSTRAINT CK_nested_friend check(UserID <> FriendID)
);

In case need a demo, see here http://sqlfiddle.com/#!3/1e405

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Hi @Rahul. I've got error: Msg 1767, Level 16, State 0, Line 1 Foreign key 'FK__UsersFrie__UserI__5BE2A6F2' references invalid table 'Users'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors. – Zvi May 09 '14 at 13:31
  • @Zvi, do you have a table called `users`? if yes, then you shouldn't get this error. – Rahul May 09 '14 at 13:37