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.

Answer 1

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

Answer 2

You should be able to create them, what method are you using to create the relationship that's giving you the error?

It could be that you're creating them backwards.

Also check if there's conflicting data in the tables. All the rows in KeyToBuilding need to be in Bldg and Key before you can create the FK relationship. Easiest to create the relationship when all 3 tables are empty of rows.