Home » RDBMS Server » Server Administration » USER_CONSTRAINTS Vs USER_INDEXS
USER_CONSTRAINTS Vs USER_INDEXS [message #318075] Mon, 05 May 2008 09:13 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi,

I have created one table with one primary key and then i queried into USER_CONSTRAINTS table it will shows the details about the primary key and also shown the index name as the constraint name in index name column.

Also i queried into USER_INDEXES it will also shown the details about the index

Here i want to know whether the index will automatically created when any table created with one primary key and what will be the behaviour ,if i explicitly create a normal index or any other index.

Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318084 is a reply to message #318075] Mon, 05 May 2008 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle implements/enforces primary key with an index this is why it creates one when you ask for a primary.
If an index that can enforce the primary key already exists then Oracle does not create a new one.

Regards
Michel
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318102 is a reply to message #318075] Mon, 05 May 2008 10:41 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
But i have been created another table with one primary key it will also create another index againts the primary key name.

As you told

If an index that can enforce the primary key already exists then Oracle does not create a new one

It should be common for all tables or for each one.

Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318104 is a reply to message #318102] Mon, 05 May 2008 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An index is owned by a table and can't be used for another one.

Regards
Michel
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318107 is a reply to message #318075] Mon, 05 May 2008 11:41 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
A primary key creates a unique index.
SQL>create table tbl(n number primary key)
SQL> /

Table created.

SQL> create unique index on tbl(N);
create unique index on tbl(N)
                    *
ERROR at line 1:
ORA-00953: missing or invalid index name


SQL> ed
Wrote file afiedt.buf

  1* create unique index indx on tbl(N)
SQL> /
create unique index indx on tbl(N)
                                *
ERROR at line 1:
ORA-01408: such column list already indexed


So you cannot create unique index or any other index for a primary key column.
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318108 is a reply to message #318107] Mon, 05 May 2008 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
missing or invalid index name

Where is the index name in your statement.

Quote:
such column list already indexed

Your column is ALREADY indexed, Oracle just says you that what you try to do is useless;

Quote:
So you cannot create unique index or any other index for a primary key column.

Wrong.
You can first create the index then the primary key.

Regards
Michel

[Updated on: Mon, 05 May 2008 11:50]

Report message to a moderator

Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318111 is a reply to message #318107] Mon, 05 May 2008 12:57 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
varu123 wrote on Mon, 05 May 2008 12:41
A primary key creates a unique index.



Not true. If an index exists already, whether is be a unique index or a non-unique index and Oracle can use it, no new index will be created. Michel kind of said this but I wanted to add the part that Oracle can use a non-unique index to enforce a primary key.
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318114 is a reply to message #318075] Mon, 05 May 2008 14:05 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I was just supporting Michel's point.

What i want to say was once a primary key is defined,you cannot create any other index for that column after wards.
Quote:

Quote:
So you cannot create unique index or any other index for a primary key column.

Wrong.
You can first create the index then the primary key.



Ofcourse yes, where/when did i say no?
Quote:

varu123 wrote on Mon, 05 May 2008 12:41
A primary key creates a unique index.



Not true. If an index exists already, whether is be a unique index or a non-unique index and Oracle can use it, no new index will be created. Michel kind of said this but I wanted to add the part that Oracle can use a non-unique index to enforce a primary key.



I didn't get your last sentence.

As Michel stated:
Quote:
If an index that can enforce the primary key already exists then Oracle does not create a new one

Only a unique index can enforce primary key functionality but not completely.
To enforce a primary key, we might require a unique key and a not null constraint to achieve the functionality of primary key.

What does enforcing a primary mean?
Essentially applying primary key functionality!!
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318115 is a reply to message #318114] Mon, 05 May 2008 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What does enforcing a primary mean?

Just that is verified and Oracle uses index (unique or not) to verify/enforce it as long as with a not null constraint.

Regards
Michel
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318117 is a reply to message #318075] Mon, 05 May 2008 14:20 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member

Quote:
Just that is verified and Oracle uses index (unique or not) to verify/enforce it as long as with a not null constraint.

It seems I am realy dumb.
What about redundancy?
How would a non unique index avoid it ?
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318118 is a reply to message #318117] Mon, 05 May 2008 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The index is just needed to efficiently access the values, it is not mandatory to enforce a primary key but this is the way Oracle efficiently enforce it.
When you insert a new value, the index is used to quickly find if the value already exists or not. It does not matter the index is itself unique or not.

Regards
Michel
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318122 is a reply to message #318075] Mon, 05 May 2008 15:21 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I know what index does.
Here we are talking in the context of primary key.
Joy Division said Oracle can use a non-unique index to enforce a primary key.
I do not understand this.

When we make a column as primary key,oracle does two things
1)make a unique index on that column
2)enforce not null constraint on that column

SO it is the unique index that enforces unique constraint on that column and hence prevent any redundant data in that column.

How would a non-unique index enforce the same functionality?
Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318123 is a reply to message #318122] Mon, 05 May 2008 15:39 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
varu123 wrote on Mon, 05 May 2008 16:21

When we make a column as primary key,oracle does two things
1)make a unique index on that column
2)enforce not null constraint on that column



Again, this is incorrect. Oracle does NOT make a unique index on a column when you add a primary key. Additionally, a unique constraint is not the same as a primary key.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

orcl10g SCOTT>create table foo (foo varchar2(80));

Table created.

orcl10g SCOTT>select * from user_indexes where table_name='FOO';

no rows selected

orcl10g SCOTT>create index foox on foo(foo);

Index created.

orcl10g SCOTT>select * from user_indexes where table_name='FOO';

INDEX_NAME INDEX_TY TABLE_ TABLE_NAME           TABLE_TYPE  UNIQUENES
---------- -------- ------ -------------------- ----------- ---------
FOOX       NORMAL   SCOTT  FOO                  TABLE       NONUNIQUE

orcl10g SCOTT>alter table foo add constraint foop primary key (foo);

Table altered.

orcl10g SCOTT>select * from user_indexes where table_name='FOO';

INDEX_NAME INDEX_TY TABLE_ TABLE_NAME           TABLE_TYPE  UNIQUENES
---------- -------- ------ -------------------- ----------- ---------
FOOX       NORMAL   SCOTT  FOO                  TABLE       NONUNIQUE

orcl10g SCOTT>select CONSTRAINT_NAME,constraint_type,TABLE_NAME,INDEX_NAME from user_constraints where table_name='FOO';

CONSTRAINT_NAME  C TABLE_NAME   INDEX_NAME
---------------- - ------------ ------------
FOOP             P FOO          FOOX



[edit] ooops, typo fixed.

[Updated on: Mon, 05 May 2008 15:49]

Report message to a moderator

Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318124 is a reply to message #318075] Mon, 05 May 2008 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How would a non-unique index enforce the same functionality?
Why do you think the non-unique index is what enforces uniqueness & not the PK constraint

13:39:21 SQL> CREATE TABLE ID10T (id number, col1 varchar2(12));

Table created.

13:39:21 SQL> INSERT INTO ID10T VALUES (1,'REDUNDANCY');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (2,'IS');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (3,'BEST');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (4,'WAY');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (5,'TO');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (6,'TEACH');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (7,'SOME');

1 row created.

13:39:21 SQL> INSERT INTO ID10T VALUES (8,'FOLKS');

1 row created.

13:39:21 SQL> CREATE INDEX NOT_UNIQ_IDX ON ID10T (ID);

Index created.

13:39:21 SQL> ALTER TABLE ID10T ADD PRIMARY KEY (ID);

Table altered.

13:39:21 SQL> INSERT INTO ID10T VALUES (8,'!');
INSERT INTO ID10T VALUES (8,'!')
*
ERROR at line 1:
ORA-00001: unique constraint (BCM.SYS_C0018449) violated

13:42:23 SQL> select constraint_name, column_name from user_cons_columns where table_name = 'ID10T';

CONSTRAINT_NAME                                                                            COLUMN_NAME
------------------------------------------------------------------------------------------ --------------------------------
SYS_C0018449                                                                               ID

13:43:14 SQL> select constraint_type from user_constraints where constraint_name = 'SYS_C0018449';

CON
---
P

13:45:51 SQL> SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = 'ID10T';

INDEX_NAME       INDEX_TYPE
-------------------------------
NOT_UNIQ_IDX     NORMAL

[Updated on: Mon, 05 May 2008 16:02] by Moderator

Report message to a moderator

Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318220 is a reply to message #318122] Tue, 06 May 2008 02:47 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Another point, Oracle will automatically build a non-unique index when creating a primary key (or even unique constraint )under certain circumstances:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE foo (x NUMBER CONSTRAINT x_id PRIMARY KEY DEFERRABLE, y VARCHAR2(20));

Table created.

SQL> SELECT index_name, uniqueness
  2  FROM user_indexes
  3  WHERE table_name = 'FOO';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
X_ID                           NONUNIQUE

Re: USER_CONSTRAINTS Vs USER_INDEXS [message #318234 is a reply to message #318220] Tue, 06 May 2008 03:21 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Another point, Oracle will automatically build a non-unique index when creating a primary key (or even unique constraint )under certain circumstances:

That is a point that's worth to be put back in mind. Thumbs Up

Regards
Michel
Previous Topic: Redo Log
Next Topic: Connecting to another Oracle Server
Goto Forum:
  


Current Time: Thu Dec 08 16:42:02 CST 2016

Total time taken to generate the page: 0.08885 seconds