Re: Tough data validation problem

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 24 Mar 2001 00:41:01 +0100
Message-ID: <3ABBDF0D.559EF97B@0800-einwahl.de>

The following works with 8.1.x:

SQL>
SQL> create table test (

  2  	     n number
  3  	     , primary_flag varchar2 (1)
  4  	     , constraint test_chk check (primary_flag in ('Y', 'N'))

  5 )
  6 /

Table created.

SQL>
SQL> create unique index test_pk
  2 on test (n)
  3 /

Index created.

SQL>
SQL> -- For the function based index to work, the user must have the system privilege

SQL> -- query rewrite!
SQL> 
SQL> create unique index test_u

  2 on test (substr (translate (primary_flag, 'YN', 'Y'), 1, 1))   3 /

Index created.

SQL>
SQL> insert into test (n, primary_flag) values (1, 'Y');

1 row created.

SQL> insert into test (n, primary_flag) values (2, 'N');

1 row created.

SQL> insert into test (n, primary_flag) values (3, 'N');

1 row created.

SQL> insert into test (n, primary_flag) values (4, 'N');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> 
SQL> -- The following should give an error, right?
SQL> 
SQL> insert into test (n, primary_flag) values (5, 'Y');

insert into test (n, primary_flag) values (5, 'Y') *
ERROR at line 1:
ORA-00001: unique constraint (TESTUS.TEST_U) violated

SQL>
SQL> update test set primary_flag = 'N' where n = 1;

1 row updated.

SQL>
SQL> update test set primary_flag = 'Y' where n = 2;

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select n, primary_flag from test order by n;

         N P                                                                    
---------- -                                                                    
         1 N                                                                    
         2 Y                                                                    
         3 N                                                                    
         4 N                                                                    

SQL> spool off

It would be much easier if you would only allow the value 'Y' and null for primary_flag. Then you could just use a unique index on primary_flag without the need of a function based index.

Martin

Chris Fischer wrote:
>
> On Thu, 15 Mar 2001 19:51:47 -0800, "Daniel A. Morgan"
> <dmorgan_at_exesolutions.com> wrote:
>
> >> Hello, I've got a unique data validation requirement.
> >>
> >> I've got two tables MASTER and DETAIL.
> >>
> >> One of the columns in DETAIL is PRIMARY_FLAG.
> >>
> >> In my form, which is a typical master detail form, the user can enter
> >> a MASTER with one or more DETAILs. They must enter a value for the
> >> PRIMARY_FLAG. Allowed values are 'Y' and 'N'. One and only one
> >> detail must have a 'Y' the rest must have 'N'.
> >>
> >> How/where can I enforce this? I can't write a DB trigger because
> >> AFTER INSERT will fire for each one. I can't do it in PRE-INSERT on
> >> the DETAIL block because a later one may have the 'Y' set. I can't do
> >> it in PRE/POST insert on the MASTER block, because the MASTER might
> >> have been inserted previously and the operator is only coming back to
> >> create DETAILS.
> >>
> >> Any ideas??
> >
> >Your requirement isn't that unique. You can either use a before-insert
> >trigger to do this or do what I have seen most often which is to never
> >insert directly into a table. Pass the values to a stored procedure and
> >let it do the validation and inserting.
> >
> >Daniel A. Morgan
>
> Yes, but. Lets say I have three rows in the table already number
> 1,2,3 and number 1 is PRIMARY=Y. Now, I want to update 1 to set
> PRIMARY=N and 2 to set PRIMARY=Y, I can't do it.
Received on Fri Mar 23 2001 - 17:41:01 CST