Board index » delphi » guid field in ms sql server as primary key not unique

Marc Antheunis

Delphi Developer
Sun, 20 Nov 2005 19:06:16 GMT

guid field in ms sql server as primary key not unique

hi,
concerning ms Sql server 2000:
i have a table wich has a guid field set up as primary key
the rowguid property is set to true
as default value the newid() function is defined
now i have in this table 3 records with exactly the same guid as primary key
came to that conclusion when designing a master detail report using this
table
so does anyone has any experience with this kind of problem?
is it safe to use guid fields as primary key?
and why does the integrity check or rebuilding the indexes maintenance plan
doesn't complain about it?

tia,
marc

 
Kevin Frever

Delphi Developer
Sun, 20 Nov 2005 20:32:51 GMT

Re:guid field in ms sql server as primary key not unique


Unless you have a very specific need for using GUIDs (like multi-server
replication), you might be better off in the long run by using integers as
primary keys, IMO.  Users understand the invoice number 123456 better than
CD10F222-D754-4EC6-8BAF-46B5D9852ED2 (and a lot less to type :)

Good luck,
krf

Quote
"Marc Antheunis" <Marc.Antheu...@XLentSolutions.Com> wrote in message

news:3eddd2a3@newsgroups.borland.com...
Quote
> hi,
> concerning ms Sql server 2000:
> i have a table wich has a guid field set up as primary key
> the rowguid property is set to true
> as default value the newid() function is defined
> now i have in this table 3 records with exactly the same guid as primary
key
> came to that conclusion when designing a master detail report using this
> table
> so does anyone has any experience with this kind of problem?
> is it safe to use guid fields as primary key?
> and why does the integrity check or rebuilding the indexes maintenance
plan
> doesn't complain about it?

> tia,
> marc

Marc Antheuni

Delphi Developer
Sun, 20 Nov 2005 21:32:44 GMT

Re:guid field in ms sql server as primary key not unique


Kevin,
the reason for this is that doing so the clientapp can safely generate guids
as primkey so i do not have to make a roundtrip to the appserver to produce
a unique number
and i never use the primkeys to show to the end user, these are mine to get
things going...

"Kevin Frevert" <ke...@workdrinkingcoffee.com> schreef in bericht
news:3edde6f4$1@newsgroups.borland.com...

Quote
> Unless you have a very specific need for using GUIDs (like multi-server
> replication), you might be better off in the long run by using integers as
> primary keys, IMO.  Users understand the invoice number 123456 better than
> CD10F222-D754-4EC6-8BAF-46B5D9852ED2 (and a lot less to type :)

> Good luck,
> krf

> "Marc Antheunis" <Marc.Antheu...@XLentSolutions.Com> wrote in message
> news:3eddd2a3@newsgroups.borland.com...
> > hi,
> > concerning ms Sql server 2000:
> > i have a table wich has a guid field set up as primary key
> > the rowguid property is set to true
> > as default value the newid() function is defined
> > now i have in this table 3 records with exactly the same guid as primary
> key
> > came to that conclusion when designing a master detail report using this
> > table
> > so does anyone has any experience with this kind of problem?
> > is it safe to use guid fields as primary key?
> > and why does the integrity check or rebuilding the indexes maintenance
> plan
> > doesn't complain about it?

> > tia,
> > marc

Bill Tod

Delphi Developer
Sun, 20 Nov 2005 22:33:36 GMT

Re:guid field in ms sql server as primary key not unique


are you saying that you inserted three rows into a table with a guid
primary key and got duplicate primary key values for two of the
records if not could you please explain in more detail by the way do
you find paragraphs with no punctuation easy to read :)

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Marc Antheuni

Delphi Developer
Sun, 20 Nov 2005 23:26:17 GMT

Re:guid field in ms sql server as primary key not unique


that was exactly what i was saying
can't explain it any further since i can't reproduce it
but i defenately got 3 records with the exact same pk in the same table
how they got in there i really do not now

will be more punctuationfull next time (c&p)
cu
marc

"Bill Todd" <n...@no.com> schreef in bericht
news:nm0sdv8uqif1vbjaiaclekr376l8pbnesn@4ax.com...

Quote
> are you saying that you inserted three rows into a table with a guid
> primary key and got duplicate primary key values for two of the
> records if not could you please explain in more detail by the way do
> you find paragraphs with no punctuation easy to read :)

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

George Christoforaki

Delphi Developer
Fri, 16 Dec 2005 16:38:52 GMT

Re:guid field in ms sql server as primary key not unique


uniqueidentifier columns may contain multiple occurrences of an individual
uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are
also specified for the column.

The above was copied from the sql manual. Now from what I saw you use the
newid which the manual says that it guarantees the uniqueness and you use
the field as a pk.

Do you manipulate the key at all? Do you add extra characters?

Pls run a test. Copy the pk's value of any duplicated record and, create a
new record with this value. What would happen?

From what I understand, If everything is done from your side correctly, then
it's a case you sould report to microsoft ;-)

George Christoforakis.

Quote
"Marc Antheunis" <Marc.Antheu...@XLentSolutions.Com> wrote in message

news:3eddd2a3@newsgroups.borland.com...
Quote
> hi,
> concerning ms Sql server 2000:
> i have a table wich has a guid field set up as primary key
> the rowguid property is set to true
> as default value the newid() function is defined
> now i have in this table 3 records with exactly the same guid as primary
key
> came to that conclusion when designing a master detail report using this
> table
> so does anyone has any experience with this kind of problem?
> is it safe to use guid fields as primary key?
> and why does the integrity check or rebuilding the indexes maintenance
plan
> doesn't complain about it?

> tia,
> marc

Other Threads

1. Identity (autincrement) primary key trouble with MS SQL Server

2. MS SQL Server and trigger for autoincrement Primary key

3. Primary key or not primary key.........

4. Primary Key vs Unique Key

5. GUID as Primary Key...what do u think?

6. GUID as primary key and performance penalty

7. MS SQL Guid Fields

8. compound unique key not unique

9. Delphi 3.0 - IIS ISAPI + SQL Server (MS) + BDE = SQL Server not found errors

10. MS SQL and a mixed-case primary key.