Get Database to Acknowledge one book written by two people

1

Hello. I'm stuck in a problem I have. I populated my database for my book tables, but then I realized that the database listed one author for one book. For example, Art History book has to list two authors (author id 1) and (author id 2).

I was told to make a new table with author_id and book_id but the problem didn't go away. Can should I do to fix this problem?

I don't what do I should change to get what I want...

booklist4.PNG1030×359 14.1 KB

2

the basic idea is to have a book table and an author table then have a AuthorBook table that hooks the two of them up. That's the gist of the suggestion given to you.

3

so something like this. I'm getting error messages though when I try to make the foreign key in the inventory table:
so it's like like this:
books
PK-ISBN, edition,
Inventory
PK-ISBN edition
bookauthors
PK-authorid
FK-?
authors
PK:authorid
FK-?
users
PK:userid
FK-?
orderdetails
PK-ISBN, edition
FK-?

I'm getting error on the foreign keys parts...like "Both sides of a relationship must have the same # of columns"

4

I don't think you can setup foreign keys with temp tables or table variables. e.g. this works:

create table #1 (id1 int primary key identity(1,1), a int)
create table #2  (id2 int primary key identity(1,1), id1 int foreign key references #1(id1), b int)

but gives the error:

Skipping FOREIGN KEY constraint '#2' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

but this doesn't:

declare @1 table (id1 int primary key identity(1,1), a int)
declare @2 table  (id2 int primary key identity(1,1), id1 int foreign key references @1(id1), b int)

yields:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@1'.

5

Oh, no, I have already created the tables...so when I try to add the foreign keys...I get something like:

6

Well, the error message is correct. You have two columns on the Foreign Key table, but only one on the Primary Key table. That is not allowed.

I think you only want:

ALTER TABLE bookauthors
ADD CONSTRAINT FK_bookauthors_books foreign key (ISBN) references books(ISBN)
7

so like this, right?

message.PNG995×611 16.9 KB

8

so like...
DECLARE @books TABLE (ISBN NVARCHAR(20), edition INT, title NVARCHAR(100), publisherName NVARCHAR(50))
PRIMARY KEY: ISBN
PRIMARY KEY: EDITION
DECLARE @inventory TABLE (ISBN NVARCHAR(20), edition INT, condition CHAR(1), price MONEY,quantity INT)
FOREIGN KEY: ISBN, EDITION
DECLARE @bookauthors TABLE (ISBN NVARCHAR(20), edition INT, authorID INT)
PRIMARY KEY: AUTHORID
FOREIGN KEY: ISBN, EDITION
DECLARE @authors TABLE (authorID INT, authorName NVARCHAR(50))
FOREIGN KEY: AUTHORID
DECLARE @users TABLE (userID BIGINT, userName VARCHAR(30), password NVARCHAR(30),firstName NVARCHAR(30), lastName NVARCHAR(30), emailAddress NVARCHAR(50))
PRIMARY KEY: USER ID
DECLARE @orders TABLE (orderID BIGINT, orderDateTime DATETIME, userID BIGINT, paymentType VARCHAR(20), paymentAccountNumber NVARCHAR(50))
FOREIGN KEY: USER ID, ORDER ID
DECLARE @orderDetails TABLE (orderID BIGINT, sequence INT, ISBN NVARCHAR(20), edition INT,quantity INT, condition CHAR(1))
PRIMARY KEY: ORDER ID
FOREIGN KEY: ISBN, EDITION

9

The bookauthors table should have two foreign key refs:

one to the book table and one to the authors table. I'd probably do it something like this:

CREATE TABLE authors (
        authorId INT identity(1, 1) PRIMARY KEY
      , authorName NVARCHAR(50)
      )

CREATE TABLE publishers (
        publisherId INT identity(1, 1) PRIMARY KEY
      , publisherName NVARCHAR(50)
      )

CREATE TABLE books (
        bookId INT identity(1, 1) PRIMARY KEY
      , ISBN NVARCHAR(20)
      , publisherId INT FOREIGN KEY REFERENCES publishers(publisherId)
      )

CREATE TABLE books_authors (
        bookId INT FOREIGN KEY REFERENCES books(bookId)
      , authorId INT FOREIGN KEY REFERENCES authors(authorId)
      , PRIMARY KEY (
              bookId
            , authorId
            )
      )

and I'd probably add:

CREATE NONCLUSTERED INDEX IX_books_ISBN
        ON books(ISBN);    
CREATE NONCLUSTERED INDEX IX_books_authors_authorId_bookId
        ON books_authors(authorId, bookId)

for reverse lookups

10

I would just rather keep the tables I have...I'm having conflicts in these last tables:
DECLARE @users TABLE (userID BIGINT, userName VARCHAR(30), password NVARCHAR(30),firstName NVARCHAR(30), lastName NVARCHAR(30), emailAddress NVARCHAR(50))
PRIMARY KEY: USER ID
DECLARE @orders TABLE (orderID BIGINT, orderDateTime DATETIME, userID BIGINT, paymentType VARCHAR(20), paymentAccountNumber NVARCHAR(50))
FOREIGN KEY: USER ID, ORDER ID
DECLARE @orderDetails TABLE (orderID BIGINT, sequence INT, ISBN NVARCHAR(20), edition INT,quantity INT, condition CHAR(1))
PRIMARY KEY: ORDER ID
FOREIGN KEY: ISBN, EDITION

I don't know what I'm doing wrong.

11

You can't keep the tables you have since you can't use FKs with temp tables or table variables.

however, if for the last table you want FOREIGN KEY: ISBN, EDITION then you need to concatenate the two into one column, perhaps as a computed, persisted column and use that as the PK/FK pair.

However I would encourage you to study what I posted and adapt it to your situation. It is a more industry-standard approach and is free from the problems you are having.

12

Alternately, see the approach described here,

e.g. this works fine:

create table one (a int, b int, primary key (a,b))
create table two (a int, b int, foreign key (a,b) references one(a,b))