Home » SQL & PL/SQL » SQL & PL/SQL » Unique index?
Unique index? [message #264824] Tue, 04 September 2007 12:37 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Ora10.2.0.2 RHEL4

Does a unique index function in the same fashion as a unique constraint? I have a valid table 'members' in my prod and dev schemas. Both have the same indexes and constraints. The unique index I'm referencing is valid and on both tables. It is created to ensure unique combinations of (websiteid, emailaddress). However, I can enter duplicate rows into the members table with this query:

insert into members (memberid, emailaddress, websiteid)
values (
MEMBERS_SEQ.nextval, 'keith12@website.com', 1089);

1 row created.

select memberid, emailaddress, websiteid from members where emailaddress = 'keith12@website.com';

 MEMBERID EMAILADDRESS                                        WEBSITEID
---------- -------------------------------------------------- ----------
 123377569 [email]keith12@website.com[/email]                                        1
 123377897 [email]keith12@website.com[/email]                                       62
 123377957 [email]keith12@website.com[/email]                                     1089
 123377947 [email]keith12@website.com[/email]                                     1089
 123377949 [email]keith12@website.com[/email]                                     1089
 123377951 [email]keith12@website.com[/email]                                     1089


INDEX_NAME                     STATUS   INDEX_TYPE                  UNIQUENES
------------------------------ -------- --------------------------- ---------
EMAIL_WEBSITE_UNQ              VALID    NORMAL                      UNIQUE


select index_name, column_name from dba_ind_columns@es_prod where table_name = 'MEMBERS'
order by index_name;  2  

INDEX_NAME                     COLUMN_NAME
------------------------------ ---------------------------------------------
EMAIL_WEBSITE_UNQ              EMAILADDRESS
EMAIL_WEBSITE_UNQ              WEBSITEID


There was never a unique constraint created, just the unique index.

Thanks.

<MOD: Added CODE tags>

[Updated on: Tue, 04 September 2007 22:16] by Moderator

Report message to a moderator

Re: Unique index? [message #264827 is a reply to message #264824] Tue, 04 September 2007 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you describe is not possible.
Are you sure the constraint is enable and in validate state?
Post a test case that we can reproduce.

Regards
Michel

[Updated on: Tue, 04 September 2007 12:47]

Report message to a moderator

Re: Unique index? [message #264828 is a reply to message #264824] Tue, 04 September 2007 12:47 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
A unique index will enforce uniqueness. All setting a unique constraint does is create a unique index. Is the index actually on the members table? Issue the following command.

select table_name,column_name,COLUMN_POSITION
from user_ind_columns
where index_name='EMAIL_WEBSITE_UNQ'
order by COLUMN_POSITION;
Re: Unique index? [message #264834 is a reply to message #264824] Tue, 04 September 2007 13:20 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
That was quite interesting. I executed the below statements at suggested by Bill on both dev & prod:

select table_name,column_name,COLUMN_POSITION
from user_ind_columns@es_prod
where index_name='EMAIL_WEBSITE_UNQ'
order by COLUMN_POSITION;

PROD:
TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------------------------------------- ---------------
MEMBERS EMAILADDRESS 1
MEMBERS WEBSITEID 2

select table_name,column_name,COLUMN_POSITION
from user_ind_columns@es_dev
where index_name='EMAIL_WEBSITE_UNQ'
order by COLUMN_POSITION;

DEV:
no rows selected


For whatever reason, the user_ind_columns view did not contain column_positions for the dev environment. I dropped the index in dev and recreated it. The unique index is now functioning properly.

Thanks.
Re: Unique index? [message #264836 is a reply to message #264834] Tue, 04 September 2007 13:24 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Would you please use CODE tags so people can read your posts? After 100+ messages you should know how to do this. Do you really think the output of your query looks professional so that people can read it?
Re: Unique index? [message #264838 is a reply to message #264836] Tue, 04 September 2007 13:28 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Does this look better?


That was quite interesting. I executed the below statements at suggested by Bill on both dev & prod:

select table_name,column_name,COLUMN_POSITION
from user_ind_columns@es_prod
where index_name='EMAIL_WEBSITE_UNQ'
order by COLUMN_POSITION;


PROD:
TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------------------------------------- ---------------
MEMBERS EMAILADDRESS 1
MEMBERS WEBSITEID 2


select table_name,column_name,COLUMN_POSITION
from user_ind_columns@es_dev
where index_name='EMAIL_WEBSITE_UNQ'
order by COLUMN_POSITION;


DEV:
no rows selected


For whatever reason, the user_ind_columns view did not contain column_positions for the dev environment. I dropped the index in dev and recreated it. The unique index is now functioning properly.

Thanks.
Re: Unique index? [message #264839 is a reply to message #264834] Tue, 04 September 2007 13:30 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why these "@..."? Do you use db links to query the catalog?
It is most likely you didn't have and/or didn't query what you think.

Regards
Michel

Previous Topic: Row getting deleted from table after insertion
Next Topic: compare date (month & year)
Goto Forum:
  


Current Time: Wed Dec 07 08:59:03 CST 2016

Total time taken to generate the page: 0.10202 seconds