2012年3月19日星期一

FK Constraint question

I have two tables
GroupUsers and Alias

GroupUsers
-GroupId
-AliasId
-UserId

Alias
-AliasId
-UserId

When a user joins a Group he selects a default Alias for his group, so I have put a foreign key constraint from the groupusers table to the alias table.

Now, if an alias is delete, is there any way through a foreign key constraint to set the value to null because all I've seen is Cascade and No Action

A better solution I'm looking for would be a way to run a script that would use the value in the UserId column from the GroupUsers table to select the top alias from the Alias table with a corresponding UserId (if exists) and set it to that. I'm unsure as to if this solution is even possible.
SQL Server doesn't support the SQL standard to set a FK to NULL, you would need to implement this, or your other solution, using a DELETE trigger on the Alias table.|||We support the SET DEFAULT and SET NULL options in SQL Server 2005. So for now, you will have to implement the logic using SPs or trigger code.

没有评论:

发表评论