PRIMARY KEY CLUSTERED

[SATYR76]

I am working on creating two table. Title_Copy and Rental
I created Title_Copy as such
Create Table Title_Copy
(
Title_ID Integer
Constraint Title_ID_FK
Foreign key(Title_ID)
References Title(Title_ID),
Copy_ID Integer,
Status Varchar(15) Not Null,
Constraint Status_PK
Check (Status In ('Available','Destroyed','Rented','Reserved')),
CONSTRAINT myConstraintName PRIMARY KEY CLUSTERED
(Title_ID,Copy_ID)
);


Then when i tried to create Table Rental

Create Table Rental
(
Book_Date Datetime DEFAULT GETDATE(),
Member_ID Integer,
Constraint Member_ID1_FK
Foreign key(Member_ID)
References Member(Member_ID),
Title_ID Integer
Constraint Title_ID1_FK
Foreign key(Title_ID)
References Title_Copy(Title_ID),
Copy_ID Integer
Constraint Copy_ID1_FK
Foreign key(Copy_ID)
References Title_Copy(Copy_ID),
ACt_RET_Date Datetime ,
EXP_RET_Date Datetime DEFAULT GETDATE()+2,
CONSTRAINT mulitpleprikey PRIMARY KEY CLUSTERED
(book_Date, Member_ID, title_ID, Copy_ID)
);


It give me the error that "There are no primary or candidate keys in the referenced table 'Title_Copy' that match the referencing column list in the foreign key 'Title_ID1_FK'."

Can some one help pointy me in the right direction. I need The Title_Copy to be a composite key referencing Title_ID and Copy_ID. But when i do that and try to create the rental table it give me that error.

Thanks for any help that you can give

 

[hmckillop]

I created your tables and generated this script using Enterprise manager, seems to do what you need.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Rental_Title_Copy]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Rental] DROP CONSTRAINT FK_Rental_Title_Copy
GO

/****** Object:  Table [dbo].[Rental]    Script Date: 07/09/2004 07:51:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rental]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Rental]
GO

/****** Object:  Table [dbo].[Title_Copy]    Script Date: 07/09/2004 07:51:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Title_Copy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Title_Copy]
GO

/****** Object:  Table [dbo].[Rental]    Script Date: 07/09/2004 07:51:38 ******/
CREATE TABLE [dbo].[Rental] (
	[Book_Date] [datetime] NOT NULL ,
	[Member_ID] [int] NOT NULL ,
	[Title_ID] [int] NOT NULL ,
	[Copy_ID] [int] NOT NULL ,
	[ACt_RET_Date] [datetime] NULL ,
	[EXP_RET_Date] [datetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Title_Copy]    Script Date: 07/09/2004 07:51:39 ******/
CREATE TABLE [dbo].[Title_Copy] (
	[Title_ID] [int] NOT NULL ,
	[Copy_ID] [int] NOT NULL ,
	[Status] [varchar] (15) COLLATE Latin1_General_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Rental] ADD 
	CONSTRAINT [DF__Rental__Book_Dat__2D27B809] DEFAULT (getdate()) FOR [Book_Date],
	CONSTRAINT [DF__Rental__EXP_RET___2F10007B] DEFAULT (getdate() + 2) FOR [EXP_RET_Date],
	CONSTRAINT [mulitpleprikey] PRIMARY KEY  CLUSTERED 
	(
		[Book_Date],
		[Member_ID],
		[Title_ID],
		[Copy_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Title_Copy] ADD 
	CONSTRAINT [myConstraintName] PRIMARY KEY  CLUSTERED 
	(
		[Title_ID],
		[Copy_ID]
	)  ON [PRIMARY] ,
	CONSTRAINT [Status_PK] CHECK ([Status] = 'Reserved' or ([Status] = 'Rented' or ([Status] = 'Destroyed' or [Status] = 'Available')))
GO

ALTER TABLE [dbo].[Rental] ADD 
	CONSTRAINT [FK_Rental_Title_Copy] FOREIGN KEY 
	(
		[Title_ID],
		[Copy_ID]
	) REFERENCES [dbo].[Title_Copy] (
		[Title_ID],
		[Copy_ID]
	),
	CONSTRAINT [Member_ID1_FK] FOREIGN KEY 
	(
		[Member_ID]
	) REFERENCES [dbo].[member] (
		[member_id]
	)
GO

ALTER TABLE [dbo].[Title_Copy] ADD 
	CONSTRAINT [Title_ID_FK] FOREIGN KEY 
	(
		[Title_ID]
	) REFERENCES [dbo].[title] (
		[title_id]
	)
GO

"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

Title_ID1_FK references only Title_ID, while table Title_Copy is using compound primary key (Title_ID, Copy_ID). You can define foreign keys only over unique attribute or set of attributes (complete primary key, UNIQUE constraint).

 

[hmckillop]

essentially replace

Title_ID    Integer
     Constraint Title_ID1_FK
        Foreign key(Title_ID)
        References Title_Copy(Title_ID),
Copy_ID        Integer    
     Constraint Copy_ID1_FK
        Foreign key(Copy_ID)
        References Title_Copy(Copy_ID),

with

Title_ID    Integer,
Copy_ID        Integer,    

CONSTRAINT [FK_Rental_Title_Copy] FOREIGN KEY 
    (
        [Title_ID],
        [Copy_ID]
    ) REFERENCES [dbo].[Title_Copy] (
        [Title_ID],
        [Copy_ID]
    ),

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SATYR76]

Thank you that worked perfect