Home » SQL & PL/SQL » SQL & PL/SQL » Primary Key with a Condition (Constraints)
Primary Key with a Condition (Constraints) [message #184075] Tue, 25 July 2006 03:50 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

My table Struture

ACCOUNT_NUM     NUMBER(12)
ACCOUNT_NUM_DUP NUMBER(12)
STATUS          NUMBER(1)


I want to have primary key constraint i.e comibation which holds good for account_num , account_num_dup where status = 1.

If the status = 2 , that constraint may not be effective.

Example

account_num|account_num_dup|status
101         102             1  - > Constraint Effective
103         102             1  - > Constraint Effective
102         101             2  - > Constraint Not Effective
101         102             2  - > Constraint Not Effective

Is this possible with constraints itself or I have to write a trigger for this.

regards
Srivaths
Re: Primary Key with a Condition (Constraints) [message #184076 is a reply to message #184075] Tue, 25 July 2006 03:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
you can write a function based unique index for that.

MHE
Re: Primary Key with a Condition (Constraints) [message #184079 is a reply to message #184075] Tue, 25 July 2006 04:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't create a conditional primary key, and if you try to enforce uniqueness using triggers, you'll almost certainly fail.

You can create a Unique constraint that matches your requirement and will prevent duplication of acc_num and acc_num_dup in rows where the status = 1.
SQL> create table temp_unq  (acc_num   number,acc_num_dup  number, status  number);

Table created.

SQL> create unique index temp_unq_idx on temp_unq(case when status = 1 then acc_num||' '||acc_num_dup else null end);

Index created.

SQL> insert into temp_unq values (101,102,1);

1 row created.

SQL> insert into temp_unq values (101,102,1);
insert into temp_unq values (101,102,1)
*
ERROR at line 1:
ORA-00001: unique constraint (JOHN_DBA.TEMP_UNQ_IDX) violated

SQL> insert into temp_unq values (102,101,2);

1 row created.

SQL> insert into temp_unq values (102,101,2);

1 row created.



Re: Primary Key with a Condition (Constraints) [message #184080 is a reply to message #184076] Tue, 25 July 2006 04:23 Go to previous message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Thats gr8 of you people. Thanks a lot it worked.

I got it this way.
CREATE UNIQUE INDEX uniq_1
ON customer_potential_duplicate
           ( 
            DECODE(STATUS, 1, account_num),
            DECODE(STATUS, 1, account_num_dup)
           );



regards
Srivaths


Previous Topic: bind variables to package procedure call stored in table
Next Topic: Middle Most Record in a Table
Goto Forum:
  


Current Time: Wed Dec 07 22:20:10 CST 2016

Total time taken to generate the page: 0.14773 seconds