Re: Tough data validation problem
Date: Sat, 24 Mar 2001 00:41:01 +0100
Message-ID: <3ABBDF0D.559EF97B_at_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 Sat Mar 24 2001 - 00:41:01 CET