A pk is *both* a physical and a logical object.
- From: Cimode <cimode@xxxxxxxxxxx>
- Date: Wed, 11 Jul 2007 13:02:30 -0700
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...;)
.
- Follow-Ups:
- Re: A pk is *both* a physical and a logical object.
- From: Cimode
- Re: A pk is *both* a physical and a logical object.
- Prev by Date: Re: performance difference between UNION operator and OR in where clause Options
- Next by Date: Re: A pk is *both* a physical and a logical object.
- Previous by thread: performance difference between UNION operator and OR in where clause Options
- Next by thread: Re: A pk is *both* a physical and a logical object.
- Index(es):
Relevant Pages
|
