Problem setting up foreign keys..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem setting up foreign keys..

Author

Topic

kaledev
Starting Member

4 Posts

Posted - 2011-02-25 : 11:18:49

Hopefully someone can help me out. I have a table named CHARGES which contains a composite primary key on TerminalID and ControllerID. I have two other tables named TERMINALS, and CONTROLLERS. TERMINALS has a composite primary key on TerminalID and TerminalName (mapping an english name to an integer ID), and CONTROLLERS likewise has a composite primary key on ControllerID and ControllerName.

I figured that I should probably add foreign keys to my CHARGES table to reference back TerminalID and ControllerID to it's respective tables. I seem to be having a bit of trouble..I am not certain how to go about doing so. I attempted:


ALTER TABLE dbo.CHARGES
ADD CONSTRAINT fk1_CHARGES
FOREIGN KEY (ControllerID)
REFERENCES dbo.CONTROLLERS(ControllerID)

I recieved the error: There are no primary or candidate keys in the referenced table 'dbo.CONTROLLERS' that match the referencing column list in the foreign key 'fk1_CHARGES'.

Can anyone help me with this? Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-02-25 : 13:13:42

please post the DDL of the Tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

kaledev
Starting Member

4 Posts

Posted - 2011-02-25 : 14:03:34

quote:
Originally posted by X002548

please post the DDL of the Tables

Posted below...These particular ones were upsized in Access, who apparently added a whole lot of extra properties that I excluded because it would take up pages.

CHARGES:


USE [EmptyInventory]
GO

/****** Object:  Table [dbo].[CHARGES]    Script Date: 02/25/2011 13:38:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CHARGES](
	[TerminalID] [smallint] NOT NULL,
	[Daily Charge] [money] NOT NULL,
	[Overage Charge] [money] NOT NULL,
	[ControllerID] [smallint] NOT NULL,
 CONSTRAINT [aaaaaCHARGES_PK] PRIMARY KEY NONCLUSTERED 
(
	[TerminalID] ASC,
	[ControllerID] 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].[CHARGES] ADD  CONSTRAINT [DF__CHARGES__Daily C__3F466844]  DEFAULT ((0)) FOR [Daily Charge]
GO

ALTER TABLE [dbo].[CHARGES] ADD  CONSTRAINT [DF__CHARGES__Overage__403A8C7D]  DEFAULT ((0)) FOR [Overage Charge]
GO

TERMINALS:


USE [EmptyInventory]
GO

/****** Object:  Table [dbo].[TERMINALS]    Script Date: 02/25/2011 13:50:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TERMINALS](
	[TerminalID] [int] IDENTITY(1,1) NOT NULL,
	[TerminalName] [varchar](255) NOT NULL,
 CONSTRAINT [aaaaaTERMINALS_PK] PRIMARY KEY NONCLUSTERED 
(
	[TerminalID] ASC,
	[TerminalName] 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

CONTROLLERS:


USE [EmptyInventory]
GO

/****** Object:  Table [dbo].[CONTROLLERS]    Script Date: 02/25/2011 13:50:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CONTROLLERS](
	[ControllerID] [smallint] IDENTITY(1,1) NOT NULL,
	[ControllerName] [varchar](255) NOT NULL,
	[ShortName] [varchar](255) NOT NULL,
 CONSTRAINT [aaaaaCONTROLLERS_PK] PRIMARY KEY NONCLUSTERED 
(
	[ControllerID] ASC,
	[ControllerName] 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources