covering Primary Keys.. What's too much

I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
ChrisWhatever has to be unique must be constrained to be unique. IOW, if your
business rules require that this table have a 6-col combination that is
unique, then you should make that unique - either via a PRIMARY KEY or
UNIQUE constraint. That said, if you will have other tables referencing
this table, then it would make sense to create a surrogate key and then use
that key in the FOREIGN KEY constraints.
That said, consider the surrogate key anyway and cluster on it. Make it,
say, your PK. Then, create a UNIQUE constraint on the 6 col combo. Inserts
into the table - if you use an identity for the PK, will perform well and
the table (i.e. clustered index) will not need to be defragged. The
nonclustered index on the UNIQUE constraint would likely need a fill factor
< 100 to mitigate fragmentation.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
Chris|||This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||If that is the case, then you're probably fine without the surrogate key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||Thanks again.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eebldFnhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> If that is the case, then you're probably fine without the surrogate key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
> This table holds only FK's and could be considered a "detail" table.
> It's also almost completely a "read" table. Writing is completed and then
> done again very seldomly.
> If I add an "ID" column as a PK, it will never be referenced by another
> table. [No FK's will exist], which makes me lean more towards the 6-key PK
> option. Does this sway your answer at all?
> I appreciate your help. After re-reading your response, the solution seems
> clear. What do you think?
> -Chris
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
>|||>> I have a table which I'd like to create a Primary Key that would have to cove
r 6 fields [sic] to enforce uniqueness. My question is, is that too much. <<
This is rare but possible. Since we do not have any specs or knwo
anything about the problem, all we can do is generalize. I have seen
some people create super-keys (sertial number + manufacturer, not
knowning that serial number has manufacturer in it).
1) it is not a surrogate key because it is exposed to the user; that is
the definition from Dr. Codd.
2) you will need to use a UNIQUE (c1,.. ,c6) constraint anyway, so you
now have two indexes
3) You will need code to assure that the IDENTITY is always validate
and points to the right target.