SQL Server constraint to permit "two unique" values

Question

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) )

Answer 1

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)