Create Table Issues

[Ghaaroth]

I'm making a simple DB to track books that are checked out. I build my
tables in the following order:

CREATE TABLE Publisher(
PublisherID AUTOINCREMENT,
Name Char(255) Not Null,
CONSTRAINT PubPK PRIMARY KEY (PublisherID)
);

CREATE TABLE Author(
AuthorID AUTOINCREMENT,
Name Char(255) Not Null,
CONSTRAINT AuthPK PRIMARY KEY (AuthorID)
);

CREATE TABLE Student(
StudentID AUTOINCREMENT,
Name Char(255) Not Null,
Email Char(255) Not Null,
CONSTRAINT StdPK PRIMARY KEY (StudentID)
);

CREATE TABLE Book(
BookID AUTOINCREMENT,
AuthorID Integer Unique Not Null,
PublisherID Integer Unique Not Null,
Name Char(255) Not Null,
ISBN Char(13),
Total_Count Integer Not Null,
Current_Count Integer Not Null,
CONSTRAINT BookPK PRIMARY KEY (BookID, AuthorID, PublisherID),
CONSTRAINT AuthFK FOREIGN KEY (AuthorID) REFERENCES Author (AuthorID),
CONSTRAINT PubFK FOREIGN KEY (PublisherID) REFERENCES Publisher
(PublisherID)
);

CREATE TABLE Transactions(
TransID AUTOINCREMENT,
BookID Integer Unique Not Null,
StudentID Integer Unique Not Null,
Lend_Dt DATE Not Null,
Return_Dt DATE,
CONSTRAINT TransPK PRIMARY KEY (TransID, BookID, StudentID),
CONSTRAINT BookFK FOREIGN KEY (BookID) REFERENCES Book (BookID),
CONSTRAINT StdFK FOREIGN KEY (StudentID) REFERENCES Student (StudentID)
);

Everything works great until I get to my Transactions table. I've
identified the troublesome line as:

CONSTRAINT BookFK FOREIGN KEY (BookID) REFERENCES Book (BookID),

but I really don't know what I'm messing up. Help anyone?

-G

 

[Brendan Reynolds]

When I attempt to execute those SQL statements, the error message is 'no
unique index found for the referenced field of the primary table', and
looking at the SQL statement for the Book table, sure enough there is no
unique index on BookID. The following change to the SQL statement for the
Book table resolves the immediate problem ...

strSQL = "CREATE TABLE Book(BookID AUTOINCREMENT Unique Not Null, " & _
"AuthorID Integer Unique Not Null, PublisherID Integer Unique Not
Null, " & _
"Name Char(255) Not Null, ISBN Char(13), Total_Count Integer Not
Null, " & _
"Current_Count Integer Not Null, " & _
"CONSTRAINT BookPK PRIMARY KEY (BookID, AuthorID, PublisherID), " &
_
"CONSTRAINT AuthFK FOREIGN KEY (AuthorID) REFERENCES Author
(AuthorID), " & _
"CONSTRAINT PubFK FOREIGN KEY (PublisherID) REFERENCES
Publisher(PublisherID));"
CurrentProject.Connection.Execute strSQL, , adCmdText

However, this looks odd to me. While you originally had no unique index on
the BookID field, you did have unique indexes on the AuthorID and
PublisherID fields. Would this not imply that each author could apear only
once, and each publisher could appear only once, in the Books table? Is that
really what you intended?

 

[Ghaaroth]

That worked. I don't know why, but I guess I had assumed AUTOINCREMENT
implied unique-ness. Also, good call on the removal of the UNIQUE
property on the foreign keys. I'm still new to all of this so I wasn't
sure if there was some way special way I need to indicate that the
"inherited" key is an AUTOINCREMENT field, or if integer gets it done.
AKA should I signify these foreign keys in a different way ?

Thanks !!

-G

 

[Brendan Reynolds]

You don't have to do anything special with the foreign keys. As far as the
table that contains the foreign key is concerned, they are just integers.
It's only the table in which they are primary keys that needs to 'know' that
they are auto-increment fields.