0

Apologies in advance that this is definitely a beginner issue; but I'm genuinely having trouble finding the solution.

In the chart linked here, I am told which columns must be linked to other tables. chart

However, I'm stumped with the Books table because it needs to have 2 keyed columns. One is the primary key (Book ID), which I've already set up. But the Title is also supposed to be keyed with the Book ID in the Book Copies table. I tried to specify Book ID as a foreign key that references the Title in Books and got an error message saying

No primary or candidate key are referenced in 'Books'

This is because I didn't know how to set Title as a key, since one already exists in that table. My table creation code for Books and Book Copies is shown below. The foreign key link doesn't work right now because I'm unsure how to reference a column that is not a primary key. If someone could help me out with how to set "Title" in "Books" as a non-primary key that can be referenced by "Book ID" in "Book Copies", I'd truly appreciate it.

Thanks!!

CREATE TABLE Books 
(
    Book_ID INT PRIMARY KEY NOT NULL IDENTITY (1,1),
    Title VARCHAR(50) NOT NULL 
    Publisher_NAME VARCHAR(50) NOT NULL 
        CONSTRAINT fk_Publisher_Name 
            FOREIGN KEY REFERENCES Publisher(Publisher_Name) 
                 ON UPDATE CASCADE ON DELETE CASCADE,
);

CREATE TABLE Book_Copies
(
    Book_ID INT NOT NULL 
        CONSTRAINT fk_Title 
        FOREIGN KEY REFERENCES Books(Title) ON UPDATE CASCADE ON DELETE CASCADE,
    Branch_ID VARCHAR(50) NOT NULL,
    Number_Of_Copies INT NOT NULL
);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FreddieMercury
  • 191
  • 1
  • 13
  • 1
    Create an "Alternate Key" aka "Candidate Key" - https://www.jorriss.net/2015/04/09/why-you-absolutely-need-alternate-keys-a-unique-constraint-story/ – user2864740 Aug 19 '18 at 18:29
  • 1
    Note that a PK is *not* a FK requirement: https://stackoverflow.com/a/18435114/2864740 – user2864740 Aug 19 '18 at 18:31
  • 1
    However, it seems that Titles would 'not make a good key'.. I stopped reading the wall of text :} – user2864740 Aug 19 '18 at 18:32
  • There's likely an error in the book; they drew the lines wrong. They want the BookId foreign keys pointing to BookId in the Book table. Logically, if they intended the FKs to be Title, they'd use something like BookTitle, they wouldn't call it BookId to reference something called Title when there's already a column named Id in Book. In a relational database like Sql Server, the idea would be to use the BookId FK in Book_Loans to fetch the Book with that Id, then you read the Title from that table, like with a JOIN. – Kevin Fichter Aug 19 '18 at 19:07
  • Thank you both, User and Kevin. I'm reviewing these articles right now, and I'm also going to double-check with my instructor to see if they did in fact do the lines wrong on the chart. I did kind of wonder if linking it to BookID wouldn't make more sense, too. Appreciate your input greatly! – FreddieMercury Aug 19 '18 at 19:20

0 Answers0