Re: Create Table Issues

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?

--
Brendan Reynolds

"Ghaaroth" <tim_ritz@xxxxxxxxx> wrote in message
news:1131849948.325417.151780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
>


.

Relevant Pages

  • Re: Check Constraints using a subquery - SSrvr 2000
    ... typically refer to the primary key of the referenced table, ... constraint or unique index references are also allowed. ... >> One usually uses a foreign key constraint to enforce a required ...
    (microsoft.public.sqlserver.programming)
  • Re: Modelling Disjoint Subtypes
    ... the only constraints one should have are foreign key references. ... I think the answer is that a foreign key constraint enforces a constraint between columns in two tables. ... these tables also have a primary key K which references K as a foreign key. ...
    (comp.databases.theory)
  • Re: Referential integrity
    ... if you have a single code table with a 2-part pk, column 1 is the code type, ... constraint to enforce one specific code type) and the 2nd column as the ... personnel_id number constraint personnel_pk primary key ... references codes ...
    (comp.databases.oracle.misc)
  • Re: unknown symbol in ER diagram
    ... main_ID INTEGER NOT NULL PRIMARY KEY ... CONSTRAINT fk__sub1 FOREIGN KEY ... REFERENCES Main ... ALTER TABLE Sub1 ADD CONSTRAINT ch__sub1 ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Naming conventions for special database objects
    ... If you want it to be required, why not use a DEFAULT and a NOT NULL constraint? ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... utility vehicles and sedans ... NOT NULL PRIMARY KEY, ...
    (comp.databases)