CODEWITHSUNDEEP
sqlsql-serverforeign-keysconstraints
Tom Heard
Tom Heard

Reputation: 1278

Adding constraints on tables with circular foreign keys

We have users in our system that have contacts attached to them. So we implemented this by having a foreign key in our contacts table that pointed back to its owning user in the users table. But now we need to add functionality that requires a user to have a default contact. So I have added a column for the default contact of a user that points to the contacts table as a foreign key. Note the tables below.

The two tables (I have chopped them down to the relevant columns):

users table:
      user_id varchar(20) PK
      default_contact bigint FK to contacts.id (nullable)

contacts table:
      id bigint PK
      user_id varchar(20) FK to users.user_id

Notice the circular Foreign Keys.

Now I want to add a constraint to the users table that says that the default_contact can only be a contact that belongs to that user (ie. the contact's user_id == the user_id of the user who has it as a default contact). I am now stuck because I am unsure how to add this constraint. This potentially could be solved by an assertion, but as I am using SqlServer assertions are not supported. This must be a common problem, because this is the second time in two days that we have come across a similar scenario in two completely different cases.

So is it even possible to add this constraint? If so, how would I go about doing so?

EDIT -- Jan 15 2014 03:39 UTC

So I have found one way I can do this, which is to add the constraint in via checks in a stored procedure. This will work as long as we always access the users.default_contact field via stored procedure, but I was wondering if there are any other strict solutions.

Upvotes: 0

Views: 306

Answers (1)

Leo
Leo

Reputation: 14820

In SQL Server you could use a combination of techniques to achieve this. A common one would be using a combination of Check Constraints and User-Defined Functions. This way when you add the constraint to the users table, everytime you perform an INSERT or UPDATE statement the constraint will validate the value specified for the default contact.

Firstly you will need to create the function...

create function ValidateContact(@userid int, @contact int)
returns bit
as
begin
   declare @count int,
           @retVal bit;

   select @count = count(*) from dbo.contacts where user_id = @userid and id = @contact

   if @count = 0 and @contact is not null
    set @retVal =  0;
    else
    set @retVal =  1;

   return @retVal;
end
go

This function will receive two parameters (userid and contact) and then checks whether the contact exists for the user. If it doesn't, it returns 0 (false) otherwise it returns 1 (true). The you will need to create the CHECk constraint...

alter table dbo.users
add constraint chk_default_contact 
check (dbo.ValidateContact(user_id, default_contact) = 1)
go

Now, everytime you perform an INSERT or UPDATE statement the CHECK constraint will validate the input based on the logic specified in the ValidateContact function, if the validation fails, an error is thrown and the transaction is aborted.

Hope it helped

Upvotes: 2

Related Questions