Professional Web Applications Themes

foreign key constraints or / and unique key ? - MySQL

I have the following database structure : my_user ( id, name ); my_country (id, name ); my_travel ( id_user , id_country , length_of_stay ); id_user AND id_country are both external keys. They references my_user and my_country. In mysql, innoDB engine, is it better to : 1/ add 2 foreign key constraints to my_travel.id_user and my_travevl.id_country or 2/ add only one unique key on (id_user, id_country) The basic fact is : when I add foreign key constraints AND unique key, mysql tells me that my_travel fields should'nt be part of an unique and foregn key in the same time. Any ideas ...

  1. #1

    Default foreign key constraints or / and unique key ?

    I have the following database structure :

    my_user ( id, name );
    my_country (id, name );
    my_travel ( id_user , id_country , length_of_stay );

    id_user AND id_country are both external keys. They references my_user
    and my_country.

    In mysql, innoDB engine, is it better to :

    1/ add 2 foreign key constraints to my_travel.id_user and
    my_travevl.id_country

    or

    2/ add only one unique key on (id_user, id_country)

    The basic fact is : when I add foreign key constraints AND unique key,
    mysql tells me that my_travel fields should'nt be part of an unique and
    foregn key in the same time.

    Any ideas ?
    Ah Bon Guest

  2. #2

    Default Re: foreign key constraints or / and unique key ?

    Ah Bon wrote:
    > I have the following database structure :
    >
    > my_user ( id, name );
    > my_country (id, name );
    > my_travel ( id_user , id_country , length_of_stay );
    >
    > id_user AND id_country are both external keys. They references my_user
    > and my_country.
    >
    > In mysql, innoDB engine, is it better to :
    >
    > 1/ add 2 foreign key constraints to my_travel.id_user and
    > my_travevl.id_country
    >
    > or
    >
    > 2/ add only one unique key on (id_user, id_country)
    >
    > The basic fact is : when I add foreign key constraints AND unique key,
    > mysql tells me that my_travel fields should'nt be part of an unique and
    > foregn key in the same time.
    >
    > Any ideas ?
    I wouldn't think you would want a unique key on id_user and id_country.
    For instance - what happens if a user makes multiple trips to the
    same country? Wouldn't you want to be able to list all the trips?


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: foreign key constraints or / and unique key ?

    > I have the following database structure :
    >
    > my_user ( id, name );
    > my_country (id, name );
    > my_travel ( id_user , id_country , length_of_stay );
    >
    > id_user AND id_country are both external keys. They references my_user
    > and my_country.
    >
    > In mysql, innoDB engine, is it better to :
    >
    > 1/ add 2 foreign key constraints to my_travel.id_user and
    > my_travevl.id_country
    >
    > or
    >
    > 2/ add only one unique key on (id_user, id_country)
    These are different functional wise.

    So there's no "better" case, you have to take what fits your
    design and requirements.

    > The basic fact is : when I add foreign key constraints AND unique key,
    > mysql tells me that my_travel fields should'nt be part of an unique and
    > foregn key in the same time.

    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  4. #4

    Default Re: foreign key constraints or / and unique key ?

    Jerry Stuckle a écrit :
    > Ah Bon wrote:
    >> I have the following database structure :
    >>
    >> my_user ( id, name );
    >> my_country (id, name );
    >> my_travel ( id_user , id_country , length_of_stay );
    >>
    >> id_user AND id_country are both external keys. They references my_user
    >> and my_country.
    >>
    >> In mysql, innoDB engine, is it better to :
    >>
    >> 1/ add 2 foreign key constraints to my_travel.id_user and
    >> my_travevl.id_country
    >>
    >> or
    >>
    >> 2/ add only one unique key on (id_user, id_country)
    >>
    >> The basic fact is : when I add foreign key constraints AND unique key,
    >> mysql tells me that my_travel fields should'nt be part of an unique
    >> and foregn key in the same time.
    >>
    >> Any ideas ?
    >
    > I wouldn't think you would want a unique key on id_user and id_country.
    > For instance - what happens if a user makes multiple trips to the same
    > country? Wouldn't you want to be able to list all the trips?
    >
    >
    In this case, I would have added another column :

    my_travel ( id_user , id_country , date_departure, length_of_stay );

    But your post made me think about the whole problem. I now consider that
    Unique is the right key to use in such design, so I'll use it.
    Ah Bon Guest

Similar Threads

  1. Unique Form inserting into many tables using unique id
    By Gabo Navarro in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: September 16th, 06:25 PM
  2. How are foreign key constraints built?
    By Wes in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: January 25th, 11:35 PM
  3. How to switch off constraints?
    By Joachim Engel in forum Informix
    Replies: 7
    Last Post: October 13th, 04:36 PM
  4. Replies: 2
    Last Post: September 16th, 03:16 AM
  5. Replies: 2
    Last Post: December 11th, 06:14 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139