Numeric Primary Key vs. Combination Primary Key

Numeric Primary Key vs. Combination Primary Key

Post by ThomasL » Wed, 16 Apr 2003 22:04:47



When do you use a sequential number as the primary key to
a table versus using the unique combination of fields to
be the primary key?

For instance, if I have to use 3 fields to uniquely
identify a record in a table which becomes the foriegn key
to another table, then those 3 fields plus another field
becomes the unique identifier of the child table, why use
a sequential number for the primary keys of these tables
when I have to make the combination of the three and four
fields to uniquely identify a record in those same tables?

Which way is faster for queries?
Which way is easier for the developers?

Etc., etc.???

Thanks,
Thomas LeBlanc

 
 
 

Numeric Primary Key vs. Combination Primary Key

Post by Tom Morea » Wed, 16 Apr 2003 22:12:45


Generally, if you have multiple columns for your primary key and you have foreign key references to it, you should consider a surrogate, single-column primary key and go with a UNIQUE constraint on the multi-column combo.  This will ease development but does have a potential for speeding up queries.  Consider a 3-column key, with, say, each column a 5-byte char.  That's 15 bytes.  Using an int would be only 4 bytes.  If the foreign key is indexed, this reduces the size of the index, thus speeding up the query.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

When do you use a sequential number as the primary key to
a table versus using the unique combination of fields to
be the primary key?

For instance, if I have to use 3 fields to uniquely
identify a record in a table which becomes the foriegn key
to another table, then those 3 fields plus another field
becomes the unique identifier of the child table, why use
a sequential number for the primary keys of these tables
when I have to make the combination of the three and four
fields to uniquely identify a record in those same tables?

Which way is faster for queries?
Which way is easier for the developers?

Etc., etc.???

Thanks,
Thomas LeBlanc

 
 
 

Numeric Primary Key vs. Combination Primary Key

Post by ThomasL » Wed, 16 Apr 2003 22:38:40


Thanks Mike,

Did you mean to say this differently?

This will ease development but does have a potential for
speeding up queries.

replacing the but with an "and":

This will ease development AND does have a potential for
speeding up queries.

Also, what do you mean "...a surrogate, single-column
primary key ..."? Give me an example, please.

Quote:>-----Original Message-----
>Generally, if you have multiple columns for your primary

key and you have foreign key references to it, you should
consider a surrogate, single-column primary key and go
with a UNIQUE constraint on the multi-column combo.  This
will ease development but does have a potential for
speeding up queries.  Consider a 3-column key, with, say,
each column a 5-byte char.  That's 15 bytes.  Using an int
would be only 4 bytes.  If the foreign key is indexed,
this reduces the size of the index, thus speeding up the
query.

>--
>Tom

>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional

>www.pinnaclepublishing.com/sql




Quote:>When do you use a sequential number as the primary key to
>a table versus using the unique combination of fields to
>be the primary key?

>For instance, if I have to use 3 fields to uniquely
>identify a record in a table which becomes the foriegn
key
>to another table, then those 3 fields plus another field
>becomes the unique identifier of the child table, why use
>a sequential number for the primary keys of these tables
>when I have to make the combination of the three and four
>fields to uniquely identify a record in those same tables?

>Which way is faster for queries?
>Which way is easier for the developers?

>Etc., etc.???

>Thanks,
>Thomas LeBlanc

 
 
 

Numeric Primary Key vs. Combination Primary Key

Post by Tom Morea » Wed, 16 Apr 2003 22:43:07


Yes, I did... and my name's not Mike.  ;-)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

Thanks Mike,

Did you mean to say this differently?

This will ease development but does have a potential for
speeding up queries.

replacing the but with an "and":

This will ease development AND does have a potential for
speeding up queries.

Also, what do you mean "...a surrogate, single-column
primary key ..."? Give me an example, please.

Quote:>-----Original Message-----
>Generally, if you have multiple columns for your primary

key and you have foreign key references to it, you should
consider a surrogate, single-column primary key and go
with a UNIQUE constraint on the multi-column combo.  This
will ease development but does have a potential for
speeding up queries.  Consider a 3-column key, with, say,
each column a 5-byte char.  That's 15 bytes.  Using an int
would be only 4 bytes.  If the foreign key is indexed,
this reduces the size of the index, thus speeding up the
query.

>--
>Tom

>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional

>www.pinnaclepublishing.com/sql




Quote:>When do you use a sequential number as the primary key to
>a table versus using the unique combination of fields to
>be the primary key?

>For instance, if I have to use 3 fields to uniquely
>identify a record in a table which becomes the foriegn
key
>to another table, then those 3 fields plus another field
>becomes the unique identifier of the child table, why use
>a sequential number for the primary keys of these tables
>when I have to make the combination of the three and four
>fields to uniquely identify a record in those same tables?

>Which way is faster for queries?
>Which way is easier for the developers?

>Etc., etc.???

>Thanks,
>Thomas LeBlanc

 
 
 

Numeric Primary Key vs. Combination Primary Key

Post by Tom Morea » Wed, 16 Apr 2003 22:50:44


As for an example, here's one:

create table MyTable
(
     PK    int            not null
                          primary key
,    ColA  char (5)       not null
,    ColB  char (5)       not null
,    ColC  varchar (2000) not null
,    constraint UK_MyTable unique (ColA, ColB)
)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

Yes, I did... and my name's not Mike.  ;-)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

Thanks Mike,

Did you mean to say this differently?

This will ease development but does have a potential for
speeding up queries.

replacing the but with an "and":

This will ease development AND does have a potential for
speeding up queries.

Also, what do you mean "...a surrogate, single-column
primary key ..."? Give me an example, please.

Quote:>-----Original Message-----
>Generally, if you have multiple columns for your primary

key and you have foreign key references to it, you should
consider a surrogate, single-column primary key and go
with a UNIQUE constraint on the multi-column combo.  This
will ease development but does have a potential for
speeding up queries.  Consider a 3-column key, with, say,
each column a 5-byte char.  That's 15 bytes.  Using an int
would be only 4 bytes.  If the foreign key is indexed,
this reduces the size of the index, thus speeding up the
query.

>--
>Tom

>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional

>www.pinnaclepublishing.com/sql




Quote:>When do you use a sequential number as the primary key to
>a table versus using the unique combination of fields to
>be the primary key?

>For instance, if I have to use 3 fields to uniquely
>identify a record in a table which becomes the foriegn
key
>to another table, then those 3 fields plus another field
>becomes the unique identifier of the child table, why use
>a sequential number for the primary keys of these tables
>when I have to make the combination of the three and four
>fields to uniquely identify a record in those same tables?

>Which way is faster for queries?
>Which way is easier for the developers?

>Etc., etc.???

>Thanks,
>Thomas LeBlanc

 
 
 

Numeric Primary Key vs. Combination Primary Key

Post by ThomasL » Thu, 17 Apr 2003 05:11:08


Quote:>-----Original Message-----
>When do you use a sequential number as the primary key to
>a table versus using the unique combination of fields to
>be the primary key?

>For instance, if I have to use 3 fields to uniquely
>identify a record in a table which becomes the foriegn
key
>to another table, then those 3 fields plus another field
>becomes the unique identifier of the child table, why use
>a sequential number for the primary keys of these tables
>when I have to make the combination of the three and four
>fields to uniquely identify a record in those same tables?

>Which way is faster for queries?
>Which way is easier for the developers?

>Etc., etc.???

>Thanks,
>Thomas LeBlanc
>.

 
 
 

1. Primary key is a combination of two keys

 Im using access and i cant keep it autonumber as the client wants the
 regionIDs for each store to start at 001.
 Infact this is not the exact problem. I have categoryid instead of region
id
 and a lot more fields to explain the category of items for sale but just to
 keep the problem concise for NG i posted the closest possible db structure.

 Regards
 NB


2. Intergrated Security Setup

3. keys vs primary keys vs indexes

4. Wanted: Paradox 3.5 for DOS

5. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

6. Control Characters

7. using big primary keys v/s small primary keys

8. how to improve query performance wihout using dynamic sql

9. Primary Key / Working Code to get primary key of a table using ADO and schemas

10. Primary key or no primary key

11. Composite Primary key or 1 primary key column with all values

12. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)