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
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