| Subject | Re: [firebird-support] Foriegn keys |
|---|---|
| Author | unordained |
| Post date | 2005-07-17T20:01:03Z |
Yes, Firebird supports it.
Composite keys do have limitations though -- it's going to want to index the data you're using for
the primary key, and composite indexing 'uses up' the index faster than single-field indexing. Ann
or Helen can give you the math (it's been posted several times) but basically, you can index two
varchar(125)'s individually, and you can index a single varchar(250), but you can't index the two
varchar(125)'s together ... that's only a problem if the composite fields of your primary key
are 'large' in some way -- text, most often. As long as you're using small fields like integers or
dates, you can fit quite a few fields together into a primary key (or foreign key, or index.)
It's a fine practice, if it makes sense. An example of where it makes sense is if you have a many-
to-many table connecting two other tables (or a table to itself) and it never makes sense to
connect two items more than once. Making the two foreign key fields (to A and to B) part of the
primary key (or, in this case, the primary key itself) makes sense. It's perfectly acceptable to do
so. The 'alter table ... add primary key ...' statement allows multiple comma-separated field names
to make part of the primary key.
On our project, we personally shun multi-field primary keys though. We'd prefer to put appropriate
unique constraints on a table (as if the fields were [part of] the primary key) but then use a
surrogate generated integer primary key to refer to rows. It's just a lot easier for us to only
ever have to deal with one field in a primary key (passing a single integer or string around is
easier than passing around an array, indicating which value goes with which column, etc.) Plus,
that makes it easier to change business rules that aren't under our control -- our pk/fk fields
stay the same, but we can change the fields and constraints on everything else. But we're lazy.
-Philip
---------- Original Message -----------
From: "women_lover_best" <talbronstien@...>
To: firebird-support@yahoogroups.com
Sent: Sun, 17 Jul 2005 03:29:50 -0000
Subject: [firebird-support] Foriegn keys
Composite keys do have limitations though -- it's going to want to index the data you're using for
the primary key, and composite indexing 'uses up' the index faster than single-field indexing. Ann
or Helen can give you the math (it's been posted several times) but basically, you can index two
varchar(125)'s individually, and you can index a single varchar(250), but you can't index the two
varchar(125)'s together ... that's only a problem if the composite fields of your primary key
are 'large' in some way -- text, most often. As long as you're using small fields like integers or
dates, you can fit quite a few fields together into a primary key (or foreign key, or index.)
It's a fine practice, if it makes sense. An example of where it makes sense is if you have a many-
to-many table connecting two other tables (or a table to itself) and it never makes sense to
connect two items more than once. Making the two foreign key fields (to A and to B) part of the
primary key (or, in this case, the primary key itself) makes sense. It's perfectly acceptable to do
so. The 'alter table ... add primary key ...' statement allows multiple comma-separated field names
to make part of the primary key.
On our project, we personally shun multi-field primary keys though. We'd prefer to put appropriate
unique constraints on a table (as if the fields were [part of] the primary key) but then use a
surrogate generated integer primary key to refer to rows. It's just a lot easier for us to only
ever have to deal with one field in a primary key (passing a single integer or string around is
easier than passing around an array, indicating which value goes with which column, etc.) Plus,
that makes it easier to change business rules that aren't under our control -- our pk/fk fields
stay the same, but we can change the fields and constraints on everything else. But we're lazy.
-Philip
---------- Original Message -----------
From: "women_lover_best" <talbronstien@...>
To: firebird-support@yahoogroups.com
Sent: Sun, 17 Jul 2005 03:29:50 -0000
Subject: [firebird-support] Foriegn keys
> Hi,------- End of Original Message -------
>
> I have situation like this..I have table with 2 foriegn keys..and I
> want to make them part of primary key(composite) in the same table.is
> it possible in firebird?If yes,is it a good practice?..
> thanks
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>