OraFAQ Forum: SQL & PL/SQL » Primary Key with a Condition (Constraints)

Home » SQL & PL/SQL » SQL & PL/SQL » Primary Key with a Condition (Constraints)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Primary Key with a Condition (Constraints) [message #184075]

Tue, 25 July 2006 03:50

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

Report message to a moderator

Re: Primary Key with a Condition (Constraints) [message #184076 is a reply to message #184075]

Tue, 25 July 2006 03:53

Maaher
Messages: 7065
Registered: December 2001

Senior Member

you can write a function based unique index for that.

MHE

Report message to a moderator

Re: Primary Key with a Condition (Constraints) [message #184079 is a reply to message #184075]

Tue, 25 July 2006 04:11

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.

Report message to a moderator

Re: Primary Key with a Condition (Constraints) [message #184080 is a reply to message #184076]

Tue, 25 July 2006 04:23

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 12:52:18 CST 2024