Creating a Composite Foreign Key in SQL Server 2008

Creating a composite foreign key in SQL Server 2008

Some of this is focused, some of this is context for others having any sort of problem like this (like anyone actually searches first?)

The first thing to check when you have a problem creating a key is make sure you did not mismatch the data types in the two tables. If you have an bigint in one and an int in the other, it will blow. This is true on all keys, but more likely to crop up if you use multiple fields. Simple math shows the reason why the chance increases.

The next issue is data. If you cannot create the key due to data, you have to find out what exists in the child table that does not exist in the parent table. LEFT JOIN the tables (secondary on the second/left side of the join) and only include rows where the primary table is null. You will either have to create these records in the parent table or get rid of them.

One way "around" this is set up a new primary key on the parent table. You then create a foreign key on this new primary key and match as many records as you can in the child table. You then have the join set up and you can go about cleaning as a secondary operation.

Which is better? New primary key or working with the composite key? This really depends on the nature of the data, but I am more fond of using a derived key over a natural key or a composite key. But, there are times where the work necessary to get a single field derived key is a lot of work.

How to create composite foreign key in sql server management studio 2012

In Object Explorer, go to your table and select Keys > New Foreign Key from the context menu:

From the dialog box that pops up, click on the Add button to create a new foreign key:

Give it a meaningful name and then click on the ... button to open the Tables and Columns specification dialog box:

Fill in the necessary columns for the parent and the child tables, click OK and you're done!

Or much easier and more efficiently - use a T-SQL script!

ALTER TABLE dbo.OtherTable
ADD CONSTRAINT FK_OtherTable_ParentTable
FOREIGN KEY(OrderId, CompanyId) REFERENCES dbo.ParentTable(OrderId, CompanyId)

Hardcoded values in composite foreign keys in SQL Server 2008 R2

You can use persisted computed columns for the type fields in LOCATION.

Computed Columns

CREATE TABLE PARTNER (
    PARTNER_TYPE nvarchar(2) CHECK(PARTNER_TYPE IN (N'CL', N'EM')),
    PARTNER nvarchar(10),
    NAME nvarchar(25),
    PRIMARY KEY (PARTNER_TYPE, PARTNER)
)

CREATE TABLE LOCATION (
    LOCATION_ID int,
    PARTNER_TYPE1 AS N'CL' PERSISTED,
    CLIENT nvarchar(10),
    PARTNER_TYPE2 AS N'EM' PERSISTED,
    EMPLOYEE nvarchar(10),
    PRIMARY KEY (LOCATION_ID),
    FOREIGN KEY (PARTNER_TYPE1, CLIENT) REFERENCES PARTNER(PARTNER_TYPE, PARTNER),
    FOREIGN KEY (PARTNER_TYPE2, EMPLOYEE) REFERENCES PARTNER(PARTNER_TYPE, PARTNER)
)

How do you create a composite foreign key when key being referenced is also a foreign key

I have no problems:

create table a(x int primary key);
create table b(x int references a, y int, primary key(x,y));
create table c(x int, y int, z int, foreign key(x,y) references b);

Adding a composite foreign key to an existing sql table

You can't create this as a single composite constraint, because it references two different tables - but it could be possible (depending on your DBMS) to add the two constraints in a single sql instruction (although I don't think it's generally recommended). You can e.g. find examples for DB2 or SQLServer.

So this could be possible:

ALTER TABLE z 
  ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES x(a) 
  ADD CONSTRAINT d FOREIGN KEY (d) REFERENCES y(b)

Creating composite foreign key constraint

It looks like you need to have your FK/references list in the same order they appear in the PK definition.

This should work:

CREATE TABLE [dbo].[ChemSampleValueTest](
    [SampleNumber] [int] NOT NULL,
    [ParameterID] [int] NOT NULL,
    [Value] [numeric](18, 6) NOT NULL,
    [Accuracy] [varchar](50) NULL,
    [ResultGroupID] [int] NOT NULL,
    [QAState] [nvarchar](32) NOT NULL,
     CONSTRAINT [PK_SampleValueTest] PRIMARY KEY CLUSTERED 
    (
        [SampleNumber] ASC,
        [ParameterID] ASC,
        [ResultGroupID] ASC
    )
) ON [PRIMARY]

CREATE TABLE [dbo].[ChemSampleValueEventLinkTest](
    [Event] [int] NOT NULL,
    [SampleNumber] [int] NOT NULL,
    [ResultGroupID] [int] NOT NULL,
    [ParameterID] [int] NOT NULL,
    [QAState] [nvarchar](32) NULL
) ON [PRIMARY]

alter table [ChemSampleValueEventLinkTest] add 
    constraint FK_ChemSampleValueEvent_ChemSampleValue_test
    foreign key ([SampleNumber], [ParameterID], [ResultGroupID]) 
    references ChemSampleValueTest ([SampleNumber], [ParameterID], [ResultGroupID])

Composite primary key with foreign key relationships to multiple tables. SQL Server 2008 Errors

This is the source of the syntax error;

the primary key in the Bldg table is :

    CONSTRAINT PK_Bldg PRIMARY KEY (bldg, [owner])

And in KeyToBuilding table, FK_KeyToBuilding_Bldg you are trying to reference ([owner], bldg) instead of (bldg, [owner])

It helps if you simply clean-up the code a bit, the following executes fine:

CREATE TABLE dbo.Owners (
    [owner] varchar(10) NOT NULL

  , CONSTRAINT PK_Owners PRIMARY KEY CLUSTERED ([owner])
) ;

CREATE TABLE dbo.[Key] (
    [owner] varchar(10) NOT NULL
  , key_id  varchar(10) NOT NULL
  , [description] varchar(10) NOT NULL

  , CONSTRAINT [PK_Key] PRIMARY KEY ([owner], key_id)
) ;

ALTER TABLE dbo.[Key] ADD CONSTRAINT FK_Key_Owners
    FOREIGN KEY([owner] REFERENCES dbo.Owners ([owner])  ;

CREATE TABLE dbo.Bldg (
    bldg    varchar(10) NOT NULL
  , [owner] varchar(10) NOT NULL

  , CONSTRAINT PK_Bldg PRIMARY KEY (bldg, [owner])
) ;

ALTER TABLE dbo.Bldg ADD CONSTRAINT FK_Bldg_Owners
    FOREIGN KEY([owner] REFERENCES dbo.Owners ([owner]);

CREATE TABLE dbo.KeyToBuilding (
    [owner] varchar(10) NOT NULL
  , bldg    varchar(10) NOT NULL
  , key_id  varchar(10) NOT NULL

    , CONSTRAINT PK_KeyToBuilding PRIMARY KEY ([owner], key_id)
);

ALTER TABLE dbo.KeyToBuilding ADD
    CONSTRAINT FK_KeyToBuilding_Key
      FOREIGN KEY([owner], key_id) REFERENCES dbo.[Key] ([owner], key_id)

  , CONSTRAINT FK_KeyToBuilding_Bldg
      FOREIGN KEY(bldg, [owner]) REFERENCES dbo.Bldg (bldg, [owner]) ;

Create a foreign key from elements of a composite primary key

Two integers are fine for a composite primary key. The syntax looks like:

 foreign key (table_id, chair_id) references translations (term_id, language_id)

Note that the columns need to be in the same order for the two column lists.

And yes, if this is a foreign key relationship, then I would suggest that you declare it to maintain data integrity.

---

Related Topics