PK with non -unique index ??
March 14, 2002 - 9am Central time zone
Reviewer: A reader
Regarding your statement "a PRIMARY KEY constraint will either create an
index (unique or non-unique depending) "
Here's my dumb question.
Please correct me if I am wrong a primary key will always
(use or) create a unique Index and never a non unique one.
SO why the statement :either create an
index (unique or non-unique depending).
Can u please give an example of PK using/creating non unique index
Thanx
Constraints and Indexes
April 22, 2002 - 10am Central time zone
Reviewer: Martin from England
Hi Tom,
Firstly, thanks for a brilliant site, the depth of knowledge and help is priceless. Just a quick
question :
You said
"
The relationship between indexes and constraints is:
o a constraint MIGHT create an index or use an existing index to efficient
enforce itself. For example, a PRIMARY KEY constraint will either create an
index (unique or non-unique depending) or it will find an existing suitable
index and use it.
o an index has nothing to do with a constraint. An index is an index.
So, a constraint MIGHT create/use and index. An INDEX is an INDEX, nothing
more, nothing less.
"
But consider the following SQL :
SQL> create table test_table ( A VARCHAR2(10) );
Table created.
SQL> create unique index A_UI on test_table(a);
Index created.
SQL> select constraint_name, constraint_type
2 from user_constraints
3 where table_name = 'TEST_TABLE';
no rows selected
SQL> INSERT INTO TEST_TABLE VALUES ('A');
1 row created.
SQL> /
INSERT INTO TEST_TABLE VALUES ('A')
*
ERROR at line 1:
ORA-00001: unique constraint (BMTEST.A_UI) violated
Is this just a bit of a herring rouge error or is this a little example of how an index ISN'T just
an index, but also a constraint (whether an "internal" constraint or otherwise)?
Followup April 22, 2002 - 10am Central time zone:
It isn't a constraint. the error message "misnames" it.
If it were a constraint, you could create a foreign key to it -- but you cannot.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table test_table ( A VARCHAR2(10), b varchar2(10) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create unique index A_UI on test_table(a)
2
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table test_table add constraint fk foreign key(b)
references test_table(a);
alter table test_table add constraint fk foreign key(b) references test_table(a)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table test_table add constraint pk primary key(a);
Table altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table test_table add constraint fk foreign key(b)
references test_table(a);
Table altered.
July 25, 2002 - 5am Central time zone
Reviewer: mike
I don't understand. If unique index can improve performance(sometimes) and enforce uniqueness, why
do we need primary key constraint?
Followup July 25, 2002 - 6am Central time zone:
A primary key MEANS SOMETHING. It conveys a specific semantic meaning.
A unique index, does not.
A primary key may or may not use a unique index. A primary key also infers (and enforces) NOT
NULL.
the underlying tools (index -- unique or not, plus NOT NULL constraint) are used to build a primary
key. A primary key means, semantically more then a unique index does.
Consider it "documentation"
Unique Index, Unique Constraint
July 25, 2002 - 7am Central time zone
Reviewer: Inga from Russia
Hello Tom!
What is the better to use to enforce uniqueness - unique index or unique constraint?
Followup July 25, 2002 - 10pm Central time zone:
unique constraint.
it has more semantic meaning
it can be deferrable if you want
it is better then a unique index
PK
August 16, 2002 - 10am Central time zone
Reviewer: Reader
Some concern about PK and Index correct me if i am wrong when we create table index is created
implicitly whether we set a PK or not...if its the case y we need to create Index explicitly if i
think that explicit index is good for performance than what do we use for imlicit one...Thanks alot
Followup August 16, 2002 - 10am Central time zone:
When you create a table, without a primary key, there will be NO index created.
An index will be created for a primary key constraint if and only if no existing index exists that
would suffice.
Unique Index and Unique Constraint
July 31, 2003 - 9pm Central time zone
Reviewer: Fan from Canada
Tom,
Considering the following two statement will achieve the same results of enforcing unique values in
column a. What do you think the benefit of the first one of having a constraint in the table?
Thanks.
1. alter table t add (constraint t_uk unique (a) using index (create unique index t_ix1 on t (a) ))
2. create unique index t_ix1 on t (a);
Previously I always used only unique indexes without unique constraints. Is it good approach?
unique index is a misnomer
August 1, 2003 - 4pm Central time zone
Reviewer: mikito harakiri
What is justification for "unique index" concept?
Index is just a B-tree. We can have functional indexes, composite indexes (which are disguised form
of functional indexes:-), bitmap indexes, etc. However, mixing constraint idea with access path
idea todether is just wrong.
Followup August 1, 2003 - 5pm Central time zone:
internally -- all indexes are unique actually.
"create unique index" -- implies the rowid is part of the functionally dependent data in the index,
the rowid is not part of the key.
"create index" -- implies the rowid is part of the key, ensuring each entry is in fact unique.
A composite or concatenated index is not a disguised form of function based indexes, I don't
understand that comment. they are just indexes with a key defined as more then one column.
But agreed -- a constraint is a constraint and should be specified as such. An index is an index
-- used to "tune"
index rows ordered also by rowid ?
August 1, 2003 - 5pm Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
> "create index" -- implies the rowid is part of the key,
> ensuring each entry is in fact unique.
Does this mean that the rows of the index are ordered
by rowid also, i.e. the indexed columns with equal
values are stored in the index sorted by rowid, and
not "randomly" ?
Followup August 1, 2003 - 7pm Central time zone:
I'll never tell ;)
it would not be anything you could ever rely on.
the only way you can ensure the order of rows from a query is to use an ORDER BY....
perhaps you may tell if I explain myself better ... ;-)
August 2, 2003 - 5am Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
> the only way you can ensure the order of rows from a query
> is to use an ORDER BY....
Of course ... actually i was thinking about a range-scan; since pinning the "pointed by the rowids"
table buffers is a relatively expensive operation (since you must get the corresponding cache
buffer latch), if the rows were ordered by rowid (and so by block, essentially), simply visiting
the index rows as they are stored would be enough to minimize the number of pins, increasing the
scalability.
So your telling would not distract me from the Road to Knowledge ;-), quite the opposite ...
Followup August 2, 2003 - 9am Central time zone:
actually -- in 9i, what you will find is the table access by rowid is moved out as far as possible
in many query plans -- meaning, we'll scan and get a bunch of rowid together and then "bulk get"
the corresponding rows -- further enhancing this concept (that is new with the cbo in 9i)
(but yes, the rowid will be the last part of the key in the b*tree with a non-unique index like
that. hypothesis is accurate)
got the point
August 2, 2003 - 9am Central time zone
Reviewer: Alberto Dell'Era from Milano, Italy
So, Oracle will try to minimize the pins of the table buffers, and whether this is done by ordering
the index rows also by rowid, or using the new "bulk get" feature of 9i you mention, it is
inessential for us, database data developers.
And the "index rows ordering by rowid" may be no more the case in 9i [even assuming that it were
performed on previous releases ...] since there's now the new "bulk get" CBO feature.
Thanks!
Very useful discussion, thanks!
September 12, 2003 - 9am Central time zone
Reviewer: David Rydzewski from Lexington, MA
How does one find the name (if any) of an index used by a PK
February 25, 2004 - 12pm Central time zone
Reviewer: mike from Minneapolis, MN
My company has created a "database normalizer" which
extracts schema information from our "perfect" database,
and uses this information to make adjustments to databases
that are being upgraded. For example, we check for missing
and/or extra indexes on tables and add/drop them as needed.
Until now, our assumption has been that a PK constraint always had an analogous entry in
USER_INDEXES, and we have
just run into a case where this is not true. Is there
a way to determine the name of an index being used by a PK
constraint if it has "chosen" one which already exists?
Followup February 25, 2004 - 12pm Central time zone:
select index_name from user_constraints ....
(what version do you have -- that was added in 9i)
Just what I was hoping for
February 25, 2004 - 12pm Central time zone
Reviewer: Mike from Minneapolis, MN
Nice to know that you have added the INDEX_NAME to
USER_CONSTRAINTS in 9i. Most of our sites are 8i,
though they will over time eventually move to newer
versions of Oracle. I think the solution is to
have our "normalizer" not panic if it is unable
to find an index in USERS_INDEXES with the same name
as the constraint. Right now we treat that as a error
condition. Thanks for your very helpful discussions
on the ORA-02270 errors. I feel much more educated
on the subject now...
Why not function-based constraints?
February 25, 2004 - 3pm Central time zone
Reviewer: Andrew from Chicago, IL USA
Muddying the constraint/index waters a bit further, I have
encountered the following requirements designing tables:
- Table rows are uniquely identified by a "name"
- Uniqueness must NOT be dependent on case
- Original case of the name must be preserved for the sake
of presentation
My typical approach in this situation is something like:
CREATE TABLE entity
( id NUMBER(10)
CONSTRAINT pk_entity
PRIMARY KEY,
name VARCHAR2(30)
CONSTRAINT nn_entity_name
NOT NULL
)
/
CREATE UNIQUE INDEX ux_entity_uppname
ON entity
( UPPER( name ) )
/
Without the luxury (necessity?) of function-based indexes,
I suppose you would be forced to define a second column,
say "uppname", and maintain it with triggers. Having the
luxury, I would rather define only the one "name" column,
add the index, and then be able to say:
--Hypothetical code
ALTER TABLE entity
ADD
( CONSTRAINT nk_entity_uppname
UNIQUE
( UPPER( name ) )
)
The compromise in my typical approach is not so much in
functionality--the unique, function-based index works like
a charm. I'm just concerned that I have an index enforcing
a database constraint that can't be declared explicitly.
Do you have a preferred approach to this situation? Thanks
in advance for any comments.
Followup February 25, 2004 - 5pm Central time zone:
Oh, I do that myself (the fbi)
when you CAN use a constraint -- by all means USE it.
when you cannot use a constraint, by all means find the most efficient method to implement your
needs - and the FBI fills that bill quite nicely.
Unique Index v/s. Unique Constraint
May 29, 2004 - 2pm Central time zone
Reviewer: Vivek Sharma from Bombay, India
Dear Tom,
Chapter 10 of Oracle 9i Documentation on Indexes states that
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm
"Oracle recommends that unique indexes be created explicitly, and not through enabling a unique
constraint on a table.
Alternatively, you can define UNIQUE integrity constraints on the desired columns. Oracle enforces
UNIQUE integrity constraints by automatically defining a unique index on the unique key. However,
it is advisable that any index that exists for query performance, including unique indexes, be
created explicitly."
What is the reason behind this ? What is the difference between the 2 methods of creating an
Indexes either directly or using a constraint ?
and does it relates to the performance ?
Thanks and Regards
Vivek
Followup May 30, 2004 - 10am Central time zone:
the recommendation is perhaps poorly worded. it should be:
"If you want a unique index, really really want a unique index, please create it explicitly because
the creation of a unique constraint or primary key won't necessary create one as most people
believe"
The difference is a constraint *might* or *might not* create an index.
The only thing that assures an index is in fact created on those exact columns, using "unique"
would be a create unique index statement.
|