SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    creating a foreign key using ALTER TABLE

    Hi there

    I have a database I want to add foreign keys using this:

    ALTER TABLE tblProduct ADD FOREIGN KEY (fldProductCatID) REFERENCES tblProductCat(fldProductCatID)

    I am using phpMyAdmin to do the query.

    My question is:

    When I export the exisitng schema will I see the foreign key query in with the 'Create Table' query?

    Cheers
    Richard

  2. #2
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when you export table from PHPMyAdmin you won't see those foreing key statements (as i already tried to do this) because MySQL doesnot support foreign keys and doesn't matter if you put those statements in. it won't affect how the tables in MySQL Works.

    i Have MySQL 4.0.12. (so, i don't know if this is still true in the newer versions or not)

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when you export table from PHPMyAdmin you won't see those foreing key statements (as i already tried to do this) because MySQL doesnot support foreign keys and doesn't matter if you put those statements in. it won't affect how the tables in MySQL Works.
    so does this mean i will not be able to create foreign keys within other tables?

    or are they existing but not visible within the schema?

    cheers

  4. #4
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can but as far as i know it doesn't matter to MySQL(until newer versions, but many hosts haven't updated tehir MySQL to newest versions). It doesn't mattert because there will be no relation. Ofcourse you would still the same thing like foregin keys and joins on table. THE ONLY DIFFERENCE WOULD BE for eg

    user table
    userid | username | password
    1 | test1 | test1
    2| test2 | test2

    user_log table
    userid | date | in | out
    1 | today | 1 | 0
    1 | today | 0 | 1
    4 | today | 1 | 0

    now in user log you can enter a userid 4 even though it doesn't exists BUT if you were using Oracle (for eg.) it would complain that because userid is foreign key therefore the userid=4 does not exist in user table so this can't be done

    MySQL wouldn't complain about that. that's only drawback otherwise you can (i thin ki should you should) do all the joins like
    select username from user,user_log where user.userid=user_log.userid and user.userid=1

    hope i am being clear

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im having all sorts of problems, I am to use MySQL and PHP for my project, below is the datamodel. Any ideas how I would incorporate the schema relationships, foreign keys etc into this?

    Cheers

    Richard


  6. #6
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well i am not a database guru but your schema is not good. because once one of my instrcutor told me whenever you see a schema which is making a CIRCLE , THINK ABOUT IT. he said you should make a schema that should not make a circle.
    so, based on that i said you should make some changes to database schema. there are other database gurus who would help you for sure.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there's nothing wrong with "circles" in data models

    i'll give you a simple example -- "employee works in department" is a zero-or-many-to-one relationship, "department is managed by employee" is a one to zero-or-one relationship, voila, circle with two entities and two relationships, but the design is perfectly valid

    richard, i think you have an extra table in there, the interests table seems wrong

    if there's a many-to-many relationship between customers and product categories, there should be only one relationship table between them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Not now, I'm kinda busy. pdxi's Avatar
    Join Date
    Dec 2004
    Location
    Oakland, California
    Posts
    784
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaswinder_rana
    when you export table from PHPMyAdmin you won't see those foreing key statements (as i already tried to do this) because MySQL doesnot support foreign keys and doesn't matter if you put those statements in. it won't affect how the tables in MySQL Works.

    i Have MySQL 4.0.12. (so, i don't know if this is still true in the newer versions or not)

    That's simply untrue. MySQL supports foreign key constraints in all versions of MySQL after 3.23.44, provided that all tables are of the InnoDB type.
    Jeffrey Hunt, freelance PHP & MySQL developer
    Resume: http://www.jeffreyhunt.org/resume/


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
  •