Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Primary with Foreign Key

    Hi Guys, I'm really confused with how the foreign key works. I'm copying a database diagram into mysql for learning purpose and I came across a strange scenario:

    Table Screens
    ================
    screenid varchar(12) <PK, FK1> NOT NULL
    hearderid varchar(12) <PK, FK2> NOT NULL
    bodyid varchar(12) <PK, FK3> NOT NULL
    comment varchar(40) NULL
    ================


    So... question. Why is there a primary key with a foreign key and why is the PK and FK together. Also, why do we have multiple PK's.

    I'm really confused any clarification would be awesome.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's take the second question first

    there is only one PK, but it is a composite key, consisting of three columns

    what that means is that the combinations of the three values are unique, even though a given value in any one of those three columns might occur multiple times

    as for the FKs, each of them is a foreign key to its own separate table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by r937 View Post
    let's take the second question first

    there is only one PK, but it is a composite key, consisting of three columns

    what that means is that the combinations of the three values are unique, even though a given value in any one of those three columns might occur multiple times

    as for the FKs, each of them is a foreign key to its own separate table

    Thank you.

    In this DB theres going to be about 10 normalized tables with similiar PK/FK relationships. Thing is that I have to MYSQLIMPORT the data first.

    So, is it best to set the PK-FK after I import the data?

    Do you have a sample schema of how such a PK-FK relationship would look in a create/alter table?

    Does this look like a good translation?

    CREATE TABLE SCREENS
    (
    screenid varchar(12),
    headerid varchar(12),
    bodyid varchar(12),
    comment varchar(40),
    primary key (screenid, headerid, bodyid),
    FOREIGN KEY (screenid) REFERENCES screens(id),
    FOREIGN KEY (headerid) REFERENCES headers(id),
    FOREIGN KEY (bodyid) REFERENCES bodyid(id)
    )
    Last edited by Eric the Red; 05-22-11 at 11:34.

  4. #4
    Join Date
    May 2008
    Posts
    97
    Does my create table look right (based on the table from the 1st post)?
    Last edited by Eric the Red; 05-22-11 at 17:28.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Eric the Red View Post
    Does my create table look right (based on the table from the 1st post)?
    if those e id columns in the referenced tables are also VARCHAR(12), then yes

    but isn't that a bit unusual? most people use numeric columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by r937 View Post
    if those e id columns in the referenced tables are also VARCHAR(12), then yes

    but isn't that a bit unusual? most people use numeric columns
    Thanks for the help! Yes it is strange that they are varchar(). I think they did this to protect the DB in case of future changes. Only thing that would make sense to me.

Posting Permissions

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