USER_CONSTRAINTS Vs USER_INDEXS [message #318075] |
Mon, 05 May 2008 09:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ORAGENASHOK
Messages: 240 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 #318102 is a reply to message #318075] |
Mon, 05 May 2008 10:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ORAGENASHOK
Messages: 240 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 #318107 is a reply to message #318075] |
Mon, 05 May 2008 11:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #318117 is a reply to message #318075] |
Mon, 05 May 2008 14:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #318122 is a reply to message #318075] |
Mon, 05 May 2008 15:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pablolee
Messages: 2882 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
|
|
|
|