SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Arrow Joining without the foreign key, is it possible?

    Just want to ask you guys if it is possible to join a tables without defining first the foreign key. Is this possible?

    BTW, what is the proper way to drop the reference key?
    could you show me.

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can join on whatever columns you want

    this is what people did long before foreign keys were invented

    (which, for mysql, was only just recently )

    which key do you want to drop? please do a SHOW CREATE TABLE and point it out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 again.

    CREATE TABLE AuthorBook
    (
    AuthID SMALLINT NOT NULL,
    BookID SMALLINT NOT NULL,
    PRIMARY KEY (AuthID, BookID),
    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    )
    ENGINE=INNODB;

    Actually i'm trying to drop a FOREIGN KEY (BookID) but the phpMyAdmin throw an error on me. because i guess the two tables are linked together.

    Do i need to disconnect them first?
    What is the proper way?

    Thank you.

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You've got a joint primary key, because you need that in this link table which solves a many to many relationship. You can't drop it in this case, as it will not make sense to use an auto-incrementing ID as an artificial key instead.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this makes perfect sense to me --

    PRIMARY KEY (AuthID, BookID),
    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    FOREIGN KEY (BookID) REFERENCES Books (BookID)

    this is exactly how you would structure the PK and FKs for this table

    why do you want to remove the BookID FK?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    because of this question of mine below.

    Just want to ask you guys if it is possible to join a tables without defining first the foreign key?
    because i want to test it.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, you will have to use this syntax:

    ALTER TABLE AuthorBook DROP FOREIGN KEY fk_symbol

    you will have to find out what fk_symbol is

    since you did not define it, mysql assigned it

    i'm not sure how to find it in phpmyadmin

    if you could drop the table and recreate it, make sure you define it

    that is, instead of this --

    ... FOREIGN KEY (BookID) REFERENCES Books (BookID)

    define it like this --

    ... CONSTRAINT AuthorBook_Books_fk FOREIGN KEY (BookID) REFERENCES Books (BookID)

    so AuthorBook_Books_fk is the fk_symbol that you define
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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