A pk is *both* a physical and a logical object.
Date: Wed, 11 Jul 2007 13:02:30 -0700
Message-ID: <1184184150.157477.288010_at_q75g2000hsh.googlegroups.com>
An extract from a post on experts-exchange.com (gosh I miss the quote of the week by Fabian PASCAL) ..A sign of our times...I invited Scott Pletchers to come explain how a primary is both a physical and logical concept...I am curious to see if he will join in...
Questionner:
In Sql Server 2000,
I have one table. I want to create Primary Key / Unique Key on one of the column of the table. But Indexes should not be created on the same column. Please help me.
angelIII:
>But Indexes should not be created on the same column.
why not? the index is there to make the check for the unique
constraint fast.
Racimo:
Primary key is a logical constraint. Index is a physical pointer
scheme for making things faster. Indexes and primary keys are indeed
separate concepts.
angelIII:
> Indexes and primary keys are indeed separate concepts.
yes, but SQL Server does not allow you to create a unique or primary
key contraint without creating an index behind the scenes, and that
for good reasons IMHO.
patrikt:
What AngelII says is the point.
How do you want SQL to check uniquenes of something without index
structure? It will not force table scan for every insert to check if
ther is duplicate key.
Even when you create Unique Key constraint SQL has to create
"something" to help force it. And it is index structure, regardless
you dont create it yourslef.
Racimo:
<<yes, but SQL Server does not allow you to create a unique or primary
key contraint without creating an index behind the scenes, and that
for good reasons IMHO.>>
Agreed.
I should have phrased that otherwise...Sorry for any confusion my
comments may have induced. I know how SQL Server works but the point
I am trying to get across is that the way SQL Server *specifically*
implements logical constraints (primary keys, uniqueness, other
constraints) by indexing is purely *platform dependent*. Only direct
image systems implement such constraints by indexing.
I guess I wanted to point out that a primary key <> *indexing* : primary key and constraints are logical concepts while indexes are physical concepts.
Regards...
ScottPletcher:
>> I guess I wanted to point out that a primary key <> *indexing* : primary key and constraints are logical concepts while indexes are physical concepts. <<
Actually a pk is *both* a physical and a logical object. On the physical side, it will include an index.
Racimo:
<<Actually a pk is *both* a physical and a logical object.>>
I am tempted to agree but no. What SQL Server does call a primary key
certainly does not redefine what a primary key is. A primary key is a
purely logical concept and nothing else. Physical implementation does
not determine *what* a primary key *is* or *should be*. It only
impacts *how* it works. A huge difference.
Regards...
Racimo:
<<I want to create Primary Key / Unique Key on one of the column of
the table. But Indexes should not be created on the same column.
Please help me.>>
To make it short...
> SQL Server does not know how to implement primary keys otherwise than by indexing
> A primary key must be *UNIQUE*, therefore to implement a primary key you either:
--> Create a clustered index with a UNIQUE + NON NULL constraints
--> Create a non clustered index with a with a UNIQUE + NON NULL constraints
ScottPletcher:
>> I am tempted to agree but no. <<
Sadly for you, disagreeing with a fact doesn't make it any less a
fact. The proof is exceedingly simple:
PKs are part of the ANSI SQL standard *for db implementations*. SQL
Server, Oracle, DB2 and MySql all have PKs, since they conform to the
base ANSI standard.
You do a disservice to people who must work in the real world as opposed to the purely theoretical one, such as questioners on this site, when you repeat falsehoods that can mislead them.
Racimo
<<Sadly for you, disagreeing with a fact doesn't make it any less a
fact. The proof is exceedingly simple:
PKs are part of the ANSI SQL standard *for db implementations*. SQL
Server, Oracle, DB2 and MySql all have PKs, since they conform to the
base ANSI standard.
You do a disservice to people who must work in the real world as opposed to the purely theoretical one, such as questioners on this site, when you repeat falsehoods that can mislead them.>> Scott,
I have respect for your posts and I may sometime agree with you but certainly not on this. As this is neither the time or place to discuss such subjects, I think I have posted the thread content in comp.databases.theory (the entire post of the thread will be there under the subject *A pk is *both* a physical and a logical object. *) for an open debate...
Feel free to join in...;) Received on Wed Jul 11 2007 - 22:02:30 CEST