2

I have a table like this one:

id   fk_id
1    1
2    1        
3    2        
4    3        
5    3

The field fk_id references another table, I want to create a constraint to permit max two insertion with each fk_id.

I want to prevent this:

id   fk_id
1    1
2    1        
3    1    <-- FAIL
4    3        
5    3

This is a relationship of "one to many (but max 2)" or "one to one (or two)" - I don´t know how I can name it.

Can I do this with MS SQL Server? Maybe a CHECK CONSTRAINT ?

SOLUTION:

-- function to check if there are more then two rows
CREATE FUNCTION [dbo].[CheckMaxTwoForeignKeys](@check_id int)
RETURNS bit
AS
BEGIN

    DECLARE @result bit
    DECLARE @count int

    SELECT @count = COUNT(*) FROM mytable WHERE fk_id = @check_id 

    IF @count <= 2
        SET @result = 1
    ELSE
        SET @result = 0

    RETURN @result

END

-- create the constraint
ALTER TABLE mytable 
ADD CONSTRAINT CK_MaxTwoFK CHECK ( ([dbo].[CheckMaxTwoForeignKeys]([fk_id])=1) )
Charles Cavalcante
  • 1,572
  • 2
  • 14
  • 27

1 Answers1

4

You should create a check constraint that calls a function; the function returns 1 if there are 2 or less values for the current value (current value that is being checked).

The check constraint should be something like check(dbo.FunctionCheckValidityOfValue = 1)

Eduard Uta
  • 2,477
  • 5
  • 26
  • 36
  • 1
    update: check(dbo.FunctionCheckValidityOfValue() = 1) – Eduard Uta Nov 17 '14 at 11:42
  • 2
    You need to pass in `fk_id` and if you make the function return the count for that value you can put the `<= n` logic right in the table definition. – Alex K. Nov 17 '14 at 12:02