Preventing Deadlock: unique

Showing posts with label unique. Show all posts

Monday, March 26, 2012

PrimaryKey and unique

how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
Looks like you have it almost right:
alter table MyTable
add
constraint PK_MyTable primary key (ClientID, UserID)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Michael Haberichter" <Haberichter@.community.nospam> wrote in message
news:ED15E772-0327-4C98-8FBD-92AE3717D1FB@.microsoft.com...
how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
sql

Primary versus unique keys

What impact is there in using a unique index instead of a primary key?

How does this impact performance?

How does this impact data file size?

Does it impact anything else?

Thanks

Hi,

http://www.mssqlcity.com/FAQ/General/primary_vs_unique_constraints.htm

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

It that the only difference?

Is there any impact on Replication, Database Mirroing (SQL 2005), or any other features?

Thanks

|||

There is an impact on transactional replication (all options) since it requires a primary key (unique indexes do not work). No impact to either merge or snapshot replication.

Database Mirroring and other features don't care.

primary keys

I am using the IDENTITY feature to generate the values fo rmy primary keys. I understand that primary keys need to be unique.

If I undestand correctly then they need to be uniquely identified in all table with in the sam database.

I am not sure if when I create a primary key using this feature. Do I need to change the seed and increment values in order to have primary keys for each table that are truely unique? This seems like the way to make all of the PK values in a database unique.

-Thanks to anyone that may be able to help

First off, a reminder that just setting an identity column doesn't make a row unique to the user. Having values:

Identity Name

1 Fred

2 Fred

Will make the two rows indistiguishable. So an Alternate Key on Name would make this far more ideal.

And yes, if you truly need your integer identity index keys to be unique in a database, a combination of seed and a check constraint will work:

create table first
(
firstId int identity(1,1) check (firstId between 1 and 100)
)
create table second
(
secondId int identity(101,1) check (secondId between 101 and 200)
)
go
insert into first default values;
insert into first default values;
insert into second default values;
insert into second default values;

select *

from first

select *

from second

firstId

secondId

101

102

Another method is to just use a guid for your keys. Of course guids are much larger and unwieldly to work with, but they are another way to go.

A big question is why do you need the values to be unique in the database? Just having them unique in the table they are in is generally good enough. It will definitely make it easier to work with since you won't have to be concerned with ranges of values later, especially if the data grows greater than initially expected.

|||

Primary key is the Domain Integrity Constraint. Here the Domain means a single table. Primary key insures the uniqueness of the table (single domain). It never validates other tables. Practically your understanding about PK is not possible. Bcs the number of records in each table is non-deterministic values. You can't make any assumption to accommodate this. Think about performance if i have more tables in my database.

But, UNIQUEIDENTIFIER typed values are used to identify the unique value across the table/database/server. Since it use the GUID on every generation the GUID must be unique.

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks for your help

Friday, March 23, 2012

primary key vs. unique index

quote:

> Hello,
> What's the difference between primary key and unique index? At this

point,

quote:

> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>

|||Hi,
If you create a primary key, by default it will create clustered index (if
there doesn't exist any) on that column(s) and if you create a unique key
then by default it will create non-clustered index on the column(s).
- Kanth
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23X%23glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...

quote:

> Hello,
> What's the difference between primary key and unique index? At this

point,

quote:

> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>

|||A primary key a logical concept which is declared for integrity reasons.
Indexes are physical artifacts added mostly for performance reasons. A table
can have only one primary key while a table can have more than one indexes.
Primary key columns must be non-nullable while unique index columns can be
nullable even though only one NULL is allowed (even for unique index on
multiple columns). Primary keys guarantee uniqueness irrespective of any
operations in the table while certain option on unique indexes can ignore
new or updated data that would create a duplicate key in the index. In SQL
Server, a declaration of primary key results in the creation of an index, by
default.
- Anith
( Please reply to newsgroups only )|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...

quote:

> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A

table

quote:

> can have only one primary key while a table can have more than one

indexes.

quote:

> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,

quote:

> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>

|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
----
"Zeng" <zzy@.nonospam.com> wrote in message
news:#X#glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...

quote:

> Hello,
> What's the difference between primary key and unique index? At this

point,

quote:

> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>

|||It's all clear to me now. Thank you everybody!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...

quote:

> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A

table

quote:

> can have only one primary key while a table can have more than one

indexes.

quote:

> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,

quote:

> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>

primary key vs. unique index

Hello,
What's the difference between primary key and unique index? At this point,
they are the same to me, but I could be wrong. Thank you in advance for
your comments.When you create a primary key, sqlserver will implement an unique index on
the column(s).
--
-oj
http://www.rac4sql.net
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23X%23glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What's the difference between primary key and unique index? At this
point,
> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>|||Hi,
If you create a primary key, by default it will create clustered index (if
there doesn't exist any) on that column(s) and if you create a unique key
then by default it will create non-clustered index on the column(s).
- Kanth
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23X%23glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What's the difference between primary key and unique index? At this
point,
> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>|||A primary key a logical concept which is declared for integrity reasons.
Indexes are physical artifacts added mostly for performance reasons. A table
can have only one primary key while a table can have more than one indexes.
Primary key columns must be non-nullable while unique index columns can be
nullable even though only one NULL is allowed (even for unique index on
multiple columns). Primary keys guarantee uniqueness irrespective of any
operations in the table while certain option on unique indexes can ignore
new or updated data that would create a duplicate key in the index. In SQL
Server, a declaration of primary key results in the creation of an index, by
default.
--
- Anith
( Please reply to newsgroups only )|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A
table
> can have only one primary key while a table can have more than one
indexes.
> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,
by
> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
----
"Zeng" <zzy@.nonospam.com> wrote in message
news:#X#glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What's the difference between primary key and unique index? At this
point,
> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>|||It's all clear to me now. Thank you everybody!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A
table
> can have only one primary key while a table can have more than one
indexes.
> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,
by
> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>

Primary Key Vs. Not NULL Unique Key

Hi,
Can anybody tell me the difference between a
primary key constraint
and
Not NULL Unique Key constraint with clustered index
Theoritically both looks the same, but am interested to know their differences in terms of their storage and performance.
One more question that's running in my mind is, if a clustered index is created on Unique key column, what will be the index key of a NULL value
Thanks very much in advanc
GYKOne difference is that the primary key constraint is not necessarily a
clustered index. As a matter of fact, I would suggest that in most cases
the clustered index should not be a unique constraint as it will typically
cause for slow inserts. It might be better to cluster by run time criteria,
such as what are the rows that need to be referenced at the same time (more
often than not), that way when the data page is brought into RAM it is all
time well spent. Other than that, I am unaware of any difference. Anybody
else?
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||Ata
First of all I have already answered this question in programming group but
going back to your suggestions
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
I always like to say it depends so
When you create a clustered index, try to create it as a UNIQUE clustered
index, not a non-unique clustered index. The reason for this is that while
SQL Server will allow you to create a non-unique clustered index, under the
surface, SQL Server will make it unique for you by adding a 4-byte
"uniqueifer" to the index key to guarantee uniqueness. This only serves to
increase the size of the key, which increases disk I/O, which reduces
performance. If you specify that your clustered index is UNIQUE when it is
created, you will prevent this unnecessary overhead.
"Ata" <NO_SPAMar_alias001@.NO_SPAMparvan.net> wrote in message
news:pOPKb.10951$JQ1.8335@.pd7tw1no...
> One difference is that the primary key constraint is not necessarily a
> clustered index. As a matter of fact, I would suggest that in most cases
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
>
> --
> Ata R
> Parvan Consulting Inc
> NO_SPAMar_alias001@.NO_SPAMparvan.net
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> > Hi,
> >
> > Can anybody tell me the difference between a
> >
> > primary key constraint
> > and
> > Not NULL Unique Key constraint with clustered index.
> >
> > Theoritically both looks the same, but am interested to know their
> differences in terms of their storage and performance.
> >
> > One more question that's running in my mind is, if a clustered index is
> created on Unique key column, what will be the index key of a NULL value?
> >
> > Thanks very much in advance
> > GYK
>|||Ata,
>It might be better to cluster by run time criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
As a side note, this behavior is often undesirable in high-volume OLTP
environments, creating very contentious pages at the 'bottom' of the table.
This phenomenon is known as 'hot-spotting'
In that case, a more intelligent choice of clustering key is needed.
James Hokes|||The primary key is a constraint held against the table
whereas the unique index is a separate object linked to
the table (not very important just changes the way they
are handled).
Conceptually the PK identifies a record whereas a unique
index just prevents duplicate values. So theoretically the
PK should never be updated (delete + insert if required)
but this is not enforced.
Some things that need to identify records will use the PK
and will not work unless one is defined.
>--Original Message--
>Hi,
>Can anybody tell me the difference between a
>primary key constraint
>and
>Not NULL Unique Key constraint with clustered index.
>Theoritically both looks the same, but am interested to
know their differences in terms of their storage and
performance.
>One more question that's running in my mind is, if a
clustered index is created on Unique key column, what will
be the index key of a NULL value?
>Thanks very much in advance
>GYK
>.
>|||Another difference is that you may ONLY have ONE PK constraint on a table,
but you may have MANY unique constraints.;
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||James,
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
Which behaviour is "this behavior". Is hot-spotting having a unique
clustered index or is it the approach I was suggesting. Pls explain?
Ata.
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:OQ86tvS1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Ata,
> >It might be better to cluster by run time criteria,
> > such as what are the rows that need to be referenced at the same time
> (more
> > often than not), that way when the data page is brought into RAM it is
all
> > time well spent. Other than that, I am unaware of any difference.
> Anybody
> > else?
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
> In that case, a more intelligent choice of clustering key is needed.
> James Hokes
>|||Another problem of non-unique clustered indexes is when
non-clustered indexes are also on the table. Another
respondent pointed out the additional overhead storing the
keys. Another issue is where you want to reindex either by
BDReindex or by Creating Index with the Drop existing
option. Doing either will require the non-clustered
indexes also be recreated affecting downtime/usability of
the table during the operation.sql

primary key vs unique key, and indexing

Hi,
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>|||More discussion about this question.
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=17b7877f-6978-4038-bc16-0c426b6cf7eb&sloc=en-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
> frederic.nourry@.gmail.com,
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
> > Hi,
> >
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> >
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> > if so do i need to create an index on the 3 fields too or the unique
> > constraint is being indexed anyway ?
> >
> > Thanks
> >
> >|||Yes, I stand corrected again :) Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...
> frederic.nourry@.gmail.com,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things

primary key vs unique key, and indexing

Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things

Primary key vs Unique key

Hi,

Can anyone tell what are the specific scenarios where Unique key is recommened over primary key ?

While designing a database table in what all cases we should think about going for Unique key rather than a primary key.

Regards,

Amit

I sometimes find tables that have natural keys but are implemented using an identity column as the primary key. In most cases the natural key should be given a UNIQUE constraint.

|||

As per the Scenario:

Priamary Key

- When you want to keep a identifier for each row. So each row can be obtianed by the Key value.(since pk unique and not null)

Unique Key

- When you want to keep secondary identifier where already the primary exists in the same table. So each row can be obtained by your PK or UK. Unique will allow null but only once(what is the use to allow single null).

Note:

Both can be either clustered or non-clustered. When you create a constraint you are allowed to choose which type of

index you want to use for these keys.

|||

Is there something related to performance of DBMS in this regard?

What is recommended if you want to achieve optimal performance.

If PK and UK are almost similar to each other , then why do we define PK on table everytime?

Why cant we simply define UK over a table with a clustered index on it to have physical ordering of data. (PK by default defines clustered index on a table in SQL server )

What will happen if we replace PK by UK?

|||

Yes. Everyone knows that PK never allows null so we always perfers the PK for any row identifier columns. You can have UNIQUE + NOT NULL + CLUSTERED to work loke PK, the advantage is you can have more than one UNIQUE + NOT NULL key. But only one CLUSTERED is allowed per table.

If the table doesn;t have clustered index already then only the PK uses clustered index by default, otherwise the default is non-clustered.

Actualy when you create the primary key or unique key you can explicitly set the type of index. But since it is a optional clauase we always missing it..

(example)

Create Table Sampleadata

(

id int primary key nonclustered,

Name int unique clustered

)

Primary key vs Clustered Key

Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that is
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
Jamie
That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than have
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey is
constrained by that primary key, is there truly any use for it?
Regards,
Jamie
"MC" wrote:

> What do you mean?
> When creating PK you by default get clustered index on same columns. In some
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>
|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with an
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck at
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustered
index as unique as opposed to a primary key that references the cluster allow
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is completely
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a clustered
> index as unique as opposed to a primary key that references the cluster allow
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working with
> a view that must be faster and more efficient. If I can eliminate a primary
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being more
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. With
the primary key, every row could have a clustered field as null.
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I see.
The primary key will always be unique, will it not? A unique cluster can
be the only unique key in a table. There can't be both a primary key and a
unique cluster. So not having a primary key would actually result in one
less column. It would mean not having many records that could contain all
null values for that cluster. I think it answers the question. The unique
cluster is a better choice than a primary key if there are to be no foreign
keys for the table. I hope I understand it correctly.
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Narrow indexes have nothing to do with whether an index is a PRIMARY
> KEY or not.
> Maybe what you really mean is that you want to drop a *column* or
> remove a *column* from an index. But that has nothing to do with having
> a primary key or not - obviously you could just as easily put the
> PRIMARY KEY constraint on the other unique column(s) you have.
> Remember that a PRIMARY KEY is automatically indexed. There is no need
> to create another index.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
sql

Primary key vs Clustered Key

Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that i
s
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
JamieWhat do you mean?
When creating PK you by default get clustered index on same columns. In some
cases, it would be better to change that to nonclustered and then create
clustered index on diff column(s). It depends on how that table is used
(queries, fk and so on)
MC
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
> Is there an advantage to a unique clustered key over a primary key? To be
> sure, there is a primary key in a table with a unique clustered index that
> is
> being held as a clustered - non unique table [with a primary key as th
e
> unique index]. There are no foreign keys for this table and I wonder if
> it
> would improve performance to modify the table in this manner.
> --
> Regards,
> Jamie|||That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than hav
e
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey i
s
constrained by that primary key, is there truly any use for it?
--
Regards,
Jamie
"MC" wrote:

> What do you mean?
> When creating PK you by default get clustered index on same columns. In so
me
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with a
n
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck a
t
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
>|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustere
d
index as unique as opposed to a primary key that references the cluster allo
w
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
--
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
--
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is complete
ly
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with
an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck
at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
>|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a cluste
red
> index as unique as opposed to a primary key that references the cluster al
low
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working wi
th
> a view that must be faster and more efficient. If I can eliminate a prima
ry
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being mor
e
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. Wit
h
the primary key, every row could have a clustered field as null.
--
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Primary Key Violation Error

SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
GO
-----
RECORDS:
11652IORESI73756002065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26 09:53:17.237
12207IORITC
73755872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
13208IORYLM
73751692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
143389IOMINA
73755882065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
15213IOALMA
73750872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
16651IORYAC
73756712065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
174578IOBASH
73755452065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
184573IODARM
73755052065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
194579IOALQA
73750062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
20206IOBURJ
73751102065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
21209IOLEMJ
73751502065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
22647IOBABV
73756382065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
23210IOJUBC
73751432065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
24205IOJUMB
73753362065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
253418IOGHYT
73756062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
263422IOFMDX
73756702065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
274731IOOASB
73753062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
284732IOSJUM
73755692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
294724IODXMB
73751602065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
304730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
314730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
324726IOGHDB
73752632065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
334809IOMADI
73753472065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
344725IOHILJ
73757072065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
354611IOGROS
73750862065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
364727IOHATA
73749942065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
374729IOJEBA
73750122065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
384728IOHYDX
73756812065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.

> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>
|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
Anith

Primary Key Violation Error

SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
----
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
G
-----
RECORDS
----
11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
12 207 IORITC
7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
13 208 IORYLM
7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
14 3389 IOMINA
7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
15 213 IOALMA
7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
16 651 IORYAC
7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
17 4578 IOBASH
7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
18 4573 IODARM
7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
19 4579 IOALQA
7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
20 206 IOBURJ
7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
21 209 IOLEMJ
7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
22 647 IOBABV
7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
23 210 IOJUBC
7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
24 205 IOJUMB
7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
25 3418 IOGHYT
7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
26 3422 IOFMDX
7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
27 4731 IOOASB
7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
28 4732 IOSJUM
7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
29 4724 IODXMB
7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
30 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
31 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
32 4726 IOGHDB
7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
33 4809 IOMADI
7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
34 4725 IOHILJ
7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
35 4611 IOGROS
7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
36 4727 IOHATA
7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
37 4729 IOJEBA
7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
38 4728 IOHYDX
7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
----> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
--
Anith

PRIMARY key versus UNIQUE index

Hi Group
Other than the fact that PRIMARY keys seem to be defined at table
creation time - what's the difference between a primary key and a
UNIQUE index? If there is a difference, what's the typical use of a
PRIMARY key?
TIA
MarkusPrimary keys do not allow null values ... that is the only difference betwee
n
Primary key and Unique Key.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Markus Zingg" wrote:

> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||and unique key allows just one null value.
"Vadivel" wrote:
> Primary keys do not allow null values ... that is the only difference betw
een
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||Hi
Not, the other difference is that when you create a primary key, SQL Server
creates a clustered index ,as opposite UNIQUE key it creates unique
nonclustered index
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
> Primary keys do not allow null values ... that is the only difference
> between
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||From the database design point of view the PRIMARY KEY should reflect the
natural key of your data.
From the technical point of view, a PRIMARY KEY does not allow NULL's, where
as a UNIQUE Constraint / Index allows at least 1 NULL.
From my own stand point I use PRIMARY KEY constraints on the natural key and
a UNIQUE Constraint for the surrogate key eg...
CREATE TABLE currency (
id int not null identity constraint sk_currency unique clustered,
code char(3) not null constraint pk_currency primary key nonclustered
)
CREATE TABLE trade (
..
...
settlement_currency_id int not null references currency( id ),
trade_currency_id int not null references currency( id ),
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Markus Zingg" <m.zingg@.nct.ch> wrote in message
news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||Unless it's composite.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:01F43F78-1A63-4AD9-B5C0-D4F2486524C1@.microsoft.com...
> and unique key allows just one null value.
> "Vadivel" wrote:
>|||Just for the sake of completion. We can create Primary Keyws without
clustered index also. Check out
http://vadivel.blogspot.com/2006/03...el.blogspot.com
"Uri Dimant" wrote:

> Hi
> Not, the other difference is that when you create a primary key, SQL Serve
r
> creates a clustered index ,as opposite UNIQUE key it creates unique
> nonclustered index
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
>
>|||This is a default behavior for the creation of a primary key; you can
override it, and make your underlying index a nonclustered one.
CREATE TABLE foo (pkid int PRIMARY KEY NONCLUSTERED,
doe smalldatetime)
CREATE CLUSTERED INDEX clidx ON foo(doe)
I try to keep the concepts of indexes and constraints as seperate as
possible in my mind (and in my designs); one is a physical tool to
assist in database performance, the other is a logical tool to assist
in data integrity. Sometimes it's useful to have a clustered primary
key; sometimes it's not.
Stu|||Hi Tony!

> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
Interesting... I have a feeling that many people does it the other way aroun
d (identity is PK, and
natural key(s) are UQ). Is it just by habit, or do you have any particular r
eason doing it that way?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> From the database design point of view the PRIMARY KEY should reflect the
natural key of your
> data.
> From the technical point of view, a PRIMARY KEY does not allow NULL's, whe
re as a UNIQUE
> Constraint / Index allows at least 1 NULL.
> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
> CREATE TABLE currency (
> id int not null identity constraint sk_currency unique clustered,
> code char(3) not null constraint pk_currency primary key nonclustered
> )
> CREATE TABLE trade (
> ...
> ...
> settlement_currency_id int not null references currency( id ),
> trade_currency_id int not null references currency( id ),
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Markus Zingg" <m.zingg@.nct.ch> wrote in message
> news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
>|||Hi Tibor - long time no beer....
My feeling and approach is this, most tables will have a natural key that we
need to provide uniqueness for, I make the natural key the PRIMARY KEY (it
also flows naturally from the logical design), the surrogate key is there as
part of the implementation phase to improve schema performance and
scalability hence I make it a unique constraint to enforce uniqueness, and
yes - use IDENTITY to auto populate.
There are a number of occaisions where there is no practical usable natural
key and in that instance I will just make the column with the IDENTITY
property the PRIMARY KEY and not bother with the surrogate.
I never use the natural key anywhere else - there is just one copy of it and
thats in its base table, and foreign key references I use the surrogate key.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCpTmJSaGHA.4544@.TK2MSFTNGP02.phx.gbl...
> Hi Tony!
>
> Interesting... I have a feeling that many people does it the other way
> around (identity is PK, and natural key(s) are UQ). Is it just by habit,
> or do you have any particular reason doing it that way?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
>

Monday, March 26, 2012

PrimaryKey and unique

Primary versus unique keys

primary keys

Friday, March 23, 2012

primary key vs. unique index

primary key vs. unique index

Primary Key Vs. Not NULL Unique Key

primary key vs unique key, and indexing

primary key vs unique key, and indexing

Primary key vs Unique key

Primary key vs Clustered Key

Primary key vs Clustered Key

Primary Key Violation Error

Primary Key Violation Error

PRIMARY key versus UNIQUE index

Preventing Deadlock

Blog Archive

About Me