Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQl for linking tables with PK (Access 2003)

    I have two tables:

    One is tblBooks and has the following fields:
    Book ID (PK)
    Company
    Title

    The second table is a linking table used to form many to many relationships between Books and Issues
    and is called BookIssueLink. It has the following fields:
    BookIDLink (PK)
    IssueNumbLink(PK)


    I have written sql to create constraints for other tables where there are distinct
    primary keys and I define the foreign key. However, when I try to build a
    relationship in sql using these two primary keys, I get the error
    "Cannot find table or constraint".


    I tried...
    Alter Table tblBooks
    ADD Constraint BookID Foreign Key (BookID) REFERENCES BookIssueLink (BookIDLink);

    ...even though I know the BookIDLink technically isnt a "foreign" key.

    What is the sql statement to build this relationship? Is the error because of the
    multiple primary keys, or am I dong something wrong with the syntax?
    I double checked the names and they are spelled correctly.

    Also, both tables are set up with the PKs as described.

    Help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQl for linking tables with PK (Access 2003)

    You're going about it from the wrong direction: the foreign key is in BookIssueLink, and it references tblBooks.

    ALTER TABLE BookIssueLink ADD CONSTRAINT Whatever FOREIGN KEY (BookIDLink) REFERENCES tblBooks (BookID);

    BTW do you have a special reason for doing this in SQL?

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQl for linking tables with PK (Access 2003)

    Hans, I actually had it this way too and got the same message (which must mean there is something about the table set up?). I am trying to understand what is under the hood in Access while learning SQL better so I want to build all the tables and relationships I did in a database I built from scratch. I attached the database as well. I appreciate any help in understanding this, becuase it is difficult to get good sql references for Access- I guess due to the Jet engine and the MS style.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQl for linking tables with PK (Access 2003)

    The name of your table is <code>BOOKLSSUELINK</code>, not <code>BOOKISSUELINK</code> <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQl for linking tables with PK (Access 2003)

    Now that stinks. Darn these tired eyes! <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •