Re: constraint question on uniqueness

From: Chris Leonard <chris_at_databaseguy.com>
Date: Mon, 3 Dec 2001 10:52:05 -0600
Message-ID: <3c0babe1$1@post.usenet.com>

• Post for FREE via your newsreader at post.usenet.com ****

You can't use a CHECK constraint for this, since CHECK constraints can only see the row that is currently changing. However, you could do this with a trigger. The following example uses a trigger for the uniqueness constraint and a CHECK constraint to do editing of values. I hope this helps!

create table one_active_row
(test_col varchar2(10)

   constraint test_col_chk check (test_col in ('ACTIVE', 'PENDING')));

create or replace trigger validate_one_active_row after insert or update on one_active_row declare
  v_nActive number;
begin

    select count(*)
    into v_nActive
    from one_active_row
    where test_col = 'ACTIVE';
    if v_nActive > 1 then

      raise_application_error(-20001,
        'You cannot have more than one active row at a time.');

    end if;
end;
/

/* succeeds: */
insert into one_active_row values ('ACTIVE');

/* succeeds: */
insert into one_active_row values ('PENDING');

/* fails check constraint: */
insert into one_active_row values ('UNKNOWN');

/* fails trigger: */
insert into one_active_row values ('ACTIVE');

Best regards,
Chris

---

Chris Leonard
MCSE, MCDBA, MCT, OCP, CIW
The Database Guy at PPI
http://www.propoint.com
Brainbench MVP for Oracle Admin
http://www.brainbench.com

"ayrobins" <anthony_at_lumos.com> wrote in message news:ldNO7.93$eM5.6307_at_dca1-nnrp2.news.digex.net...
> Is there any way to create a constraint (probably a CHECK constraint) that
> would inforce uniqueness for
> one type of value for that column.

>

> e.g.

>


> row1, col1 = "active"

> row2, col1 = "pending"
> row3, col1 = "pending"

>

> Thus the value "active" can only be specified for one row at a time.
> "pending" will be specified for all other
> rows.

>

> thanx in advance.

>
>

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Mon Dec 03 2001 - 10:52:05 CST