Re: constraint question

On Sun, 08 Sep 2002 23:48:21 +0000, eugene kim <eugene1977_at_hotmail.com> wrote:

>hi..
>i'm a beginner sql programmer..
>
>if there's a better place to ask fundemantal sql questions like this..
>pls inform me,,
>
>my question is,
>
>create table teams(
> team_id integer primary key,
> school_id integer references schools,
> sport varchar(100),
> gender char
>);
>
>if ( school_id, sport, gender) uniquely identifies a team entity just like
>team_id can uniquely do so.
>
>is it better to have a constraint such as
>(school_id, sport, gender) unique
>in my table?
>
>well that will prevent users to insert duplicate data into table..
>
>so i guess it's better to have the constraint
>instead of forcing application programmer to check the condition..?
>
>thank you

Hi Eugene,

It depends...if your TEAMS table is referenced by other tables, it is clearly better to have a simple Integer PK (Team_id) referenced than a long composite key. Performs better and requires less space. In that case you may even create a PK constraint on Team_id AND a unique constraint on (school_id, sport, gender).

If your TEAMS table is not referenced by other tables, it may not be worth the effort to implement an integer PK. Then you could just use (school_id, sport, gender) as a PK. But consider the possibility of (school_id, sport, gender) some day not being unique any more (a school may suddenly have 2 male basket teams...and your application dies just as suddenly). Simple integer PK's taken from a sequence are always unique...

• Kenneth Koenraadt