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

Question

USE [Fk_Test2]
GO

/****** Object:  Table [dbo].[Owners]    Script Date: 08/20/2010 16:52:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Owners](
    [Owner] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Owners] PRIMARY KEY CLUSTERED 
(
    [Owner] 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

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[Key]    Script Date: 08/20/2010 16:49:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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 CLUSTERED 
(
    [owner] ASC,
    [key_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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Key]  WITH CHECK ADD  CONSTRAINT [FK_Key_Owners] FOREIGN KEY([owner])
REFERENCES [dbo].[Owners] ([Owner])
GO

ALTER TABLE [dbo].[Key] CHECK CONSTRAINT [FK_Key_Owners]
GO



/****** Object:  Table [dbo].[Bldg]    Script Date: 08/20/2010 16:50:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Bldg](
    [bldg] [varchar](10) NOT NULL,
    [owner] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Bldg] PRIMARY KEY CLUSTERED 
(
    [bldg] ASC,
    [owner] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Bldg]  WITH CHECK ADD  CONSTRAINT [FK_Bldg_Owners] FOREIGN KEY([owner])
REFERENCES [dbo].[Owners] ([Owner])
GO

ALTER TABLE [dbo].[Bldg] CHECK CONSTRAINT [FK_Bldg_Owners]
GO


/****** Object:  Table [dbo].[KeyToBuilding]    Script Date: 08/20/2010 17:13:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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 CLUSTERED 
(
    [owner] ASC,
    [bldg] ASC,
    [key_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

SET ANSI_PADDING OFF
GO

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

ALTER TABLE [dbo].[KeyToBuilding] CHECK CONSTRAINT [FK_KeyToBuilding_Key]
GO

ALTER TABLE [dbo].[KeyToBuilding]  WITH CHECK ADD  CONSTRAINT [FK_KeyToBuilding_Bldg] FOREIGN KEY([owner], [bldg])
REFERENCES [dbo].[Bldg] ([owner], [bldg])
GO

ALTER TABLE [dbo].[KeyToBuilding] CHECK CONSTRAINT [FK_KeyToBuilding_Bldg]
GO

Whenever I try to set this up I get an Error "The columns in table "Key" or "Bldg" do not match an existing primary key or UNIQUE constraint.

Am I trying to implement this relationship in the wrong way? or doing something dumb?

The system is for tracking the keys (physical) people have to different buildings. That is why there is key_id and Key they aren't database keys.

It would be easier if you posted the actual DDL used to create these tables. — codingbadger
Consider including an Owner table with relationships to Bldg and Key — Beth
I changed out my mock up of the tables to be an actual script Also the Error I get is on the FK_KeyToBuilding_Bldg it says Msg 1776, Level 16, State 0, Line 2 There are no primary or candidate keys in the referenced table 'dbo.Bldg' that match the referencing column list in the foreign key 'FK_KeyToBuilding_Bldg'. — Peter

Damir Sudarevic Damir Sudarevic · Accepted Answer · 2010-08-21T15:44:34

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

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

2 Answers