PK/UQ dumb question?

If there's a primary key constraint on a field, is it redundant to have a
unique key constraint on that same field?You may still want a unique constraint on the same table, for example
primary key on Customer_ID and unique key on SocialSecurityNumber, but there
is no need for a unique key constraint on the primary key column.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>|||Hi,
SQL Server will not allow to keep Primary key and Unique constraint on the
same field.
Thanks
Hari
SQL Server MVP
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>|||Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:uQpn1TXXFHA.3840@.tk2msftngp13.phx.gbl...
> You may still want a unique constraint on the same table, for example
> primary key on Customer_ID and unique key on SocialSecurityNumber, but
> there
> is no need for a unique key constraint on the primary key column.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for your reponse. It seems like it can be done, though.
create table test (testfld int not null)
alter table test add constraint pk_test primary key (testfld)
alter table test add constraint uq_test unique (testfld)
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:OyZaPWXXFHA.3488@.tk2msftngp13.phx.gbl...
> Hi,
> SQL Server will not allow to keep Primary key and Unique constraint on the
> same field.
> Thanks
> Hari
> SQL Server MVP
>
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Yes, it is redundant because a pk by definition have to be unique and not
null. When sql server check for integrity, it will give preference to pk.
use northwind
go
create table t1 (
c1 int not null,
constraint pk_t1 primary key (c1),
constraint u_t1_c1 unique (c1)
)
go
select
*
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
table_name = 't1'
go
insert into t1 values(1)
go
-- this will give an error because of the pk constraint
insert into t1 values(1)
go
drop table t1
go
AMB
"Paul Pedersen" wrote:

> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>
>|||I accepted. If there is primary key then the Unique constraint is redundant.
Thanks
Hari
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:OBibncXXFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reponse. It seems like it can be done, though.
>
> create table test (testfld int not null)
> alter table test add constraint pk_test primary key (testfld)
> alter table test add constraint uq_test unique (testfld)
>
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:OyZaPWXXFHA.3488@.tk2msftngp13.phx.gbl...
>|||Thank you for your help.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:eyTSShXXFHA.796@.TK2MSFTNGP09.phx.gbl...
>I accepted. If there is primary key then the Unique constraint is
>redundant.
> Thanks
> Hari
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:OBibncXXFHA.2796@.TK2MSFTNGP09.phx.gbl...
>|||Thank you.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7EAC399A-D99C-4578-B34B-82967C169247@.microsoft.com...
> Yes, it is redundant because a pk by definition have to be unique and not
> null. When sql server check for integrity, it will give preference to pk.
> use northwind
> go
> create table t1 (
> c1 int not null,
> constraint pk_t1 primary key (c1),
> constraint u_t1_c1 unique (c1)
> )
> go
> select
> *
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> table_name = 't1'
> go
> insert into t1 values(1)
> go
> -- this will give an error because of the pk constraint
> insert into t1 values(1)
> go
> drop table t1
> go
>
> AMB
>
> "Paul Pedersen" wrote:
>