SQLTeam.com | Weblogs | Forums

Get Database to Acknowledge one book written by two people

sql2014

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


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


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