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

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.

Answer 2

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 CLUSTERED ([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 CLUSTERED ( [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 CLUSTERED ([owner], [bldg], [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]) ;

Now regarding the model -- I do not understand it completely -- but if I wanted to track people, keys and buildings, this would do:

The model above does not track building owners, so the BuildingOwner table is added.

• A person can own several buildings.
• A building can be owned by several people (partnership).

A Key can have an owner too, not necessarily the person who currently has the key.

• A key can open one building, one building can be opened by many keys.
• A key has an owner.
• A key is held by a key-holder, who may or may not be the owner of the key.