I can not make a composite relationship between 2 tables in SQL Server

I have the next schema where I want to relation the tables COMPANIES_COUNTRIES and TIME_TRACKING_REQUEST_TYPE by CountryId and CompanyId.

DB Schema

The scripts for both tables are the next:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[COMPANIES_COUNTRIES]
(
    [CountryId] [int] NOT NULL,
    [CompanyId] [int] NOT NULL,
    [Active] [bit] NOT NULL,
    [DateCreated] [datetime] NULL,
    [DateUpdated] [datetime] NULL,

    CONSTRAINT [PK_COMPANIES_COUNTRIES] 
        PRIMARY KEY CLUSTERED ([CountryId] ASC, [CompanyId] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[COMPANIES_COUNTRIES] 
    ADD CONSTRAINT [DF_COMPANIES_COUNTRIES_Active] DEFAULT ((1)) FOR [Active]
GO

ALTER TABLE [dbo].[COMPANIES_COUNTRIES]  WITH CHECK 
    ADD CONSTRAINT [FK_COMPANIES_COUNTRIES_COMPANIES] 
        FOREIGN KEY([CompanyId]) REFERENCES [dbo].[COMPANIES] ([Id])
GO

ALTER TABLE [dbo].[COMPANIES_COUNTRIES] CHECK CONSTRAINT [FK_COMPANIES_COUNTRIES_COMPANIES]
GO

ALTER TABLE [dbo].[COMPANIES_COUNTRIES] WITH CHECK 
    ADD CONSTRAINT [FK_COMPANIES_COUNTRIES_COUNTRIES] 
        FOREIGN KEY([CountryId]) REFERENCES [dbo].[COUNTRIES] ([Id])
GO

ALTER TABLE [dbo].[COMPANIES_COUNTRIES] CHECK CONSTRAINT [FK_COMPANIES_COUNTRIES_COUNTRIES]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'List of countries assigned to each company' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'COMPANIES_COUNTRIES'
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TIME_TRACKING_REQUEST_TYPE]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyId] [int] NOT NULL,
    [CountryId] [int] NOT NULL,
    [Code] [nchar](3) NOT NULL,
    [Description] [nvarchar](200) NOT NULL,
    [Active] [bit] NOT NULL,

    CONSTRAINT [PK_TIME_TRACKING_REQUEST_TYPE_1] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TIME_TRACKING_REQUEST_TYPE] 
    ADD CONSTRAINT [DF_TIME_TRACKING_REQUEST_TYPE_Active]  DEFAULT ((1)) FOR [Active]
GO

I tried to make a composite relationship with next script but didn’t work:

ALTER TABLE TIME_TRACKING_REQUEST_TYPE WITH CHECK 
    ADD CONSTRAINT [FK_TIME_TRACKING_REQUEST_TYPE_COMPANY_COUNTRY] 
        FOREIGN KEY (CompanyId, CountryId) REFERENCES COMPANIES_COUNTRIES (CompanyId, CountryId);

I get this error (sorry, it is in Spanish):

Msg 1776, Level 16, State 0, Line 1
No hay claves principales ni candidatas en la tabla a la que se hace referencia (‘COMPANIES_COUNTRIES’) que concuerden con la lista de columnas que hace la referencia en la clave externa ‘FK_TIME_TRACKING_REQUEST_TYPE_COMPANY_COUNTRY’.

Msg 1750, Level 16, State 0, Line 1
No se pudo crear la restricción o el índice. Vea los errores anteriores.

I thought to put both fields as UNIQUE, but I can not due I can repeat records for the same company and country.

Do you why is happening that?

Thanks in advance.

Answer

Foreign keys reference unique “identifiers”. In your case, your identifier is the primary key (CountryId, CompanyId) on COMPANIES_COUNTRIES. Your foreign key should point to that and not to (CompanyId, CountryId)

ALTER TABLE [dbo].[TIME_TRACKING_REQUEST_TYPE] ADD CONSTRAINT [FK_TIME_TRACKING_REQUEST_TYPE_COMPANY_COUNTRY] 
    FOREIGN KEY (CountryId, CompanyId) 
    REFERENCES COMPANIES_COUNTRIES (CountryId, CompanyId);