How to create a check (Table A.Id and Table B.Id and TypeId = 1)?

Question

I need a relationship between a contract (CompanyId) and a company (CompanyId) with a company type is equal to 1.

ALTER TABLE [dbo].[Contract]  WITH CHECK ADD  CONSTRAINT [FK_Contract_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId]) WHERE CompanyTypeId = 1

Answer 1

You can't have a where clause on a foreign key constraint.
However, you can use a regular foreign key constraint and also a check constraint, where your check expression will be if the CompanyTypeId is actually 1 for the CompanyId that you try to insert into contract.

To do that you will need to create a user defined function that will do the actual test and return a value. for example:

CREATE FUNCTION CheckCompanyType 
(
    @CompanyId int, 
    @AllowdCompanyType int)
RETURNS int
AS
BEGIN
    DECLARE @RetVal int
    SELECT @RetVal = COUNT(*)
    FROM Company
    WHERE CompanyId = @CompanyId 
    AND CompanyTypeId = @AllowdCompanyType 
    RETURN @RetVal
END

And then in your check constraint you should execute this function and check if the returned value is higher then 0 (should probably be 0 or 1 in your case):

ALTER TABLE dbo.[Contract]
ADD  CONSTRAINT CHK_Contract_Company 
     CHECK (dbo.CheckCompanyType(CompanyId, 1) > 0);