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.