## Numeric Primary Key vs. Combination Primary Key

### Numeric Primary Key vs. Combination Primary Key

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

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

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

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

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

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
>.

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