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 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.
jstucklex@attglobal.net
==================

> 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
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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.