Home » SQL & PL/SQL » SQL & PL/SQL » unique(field1,field2)where field2 is not null
unique(field1,field2)where field2 is not null [message #224571] Wed, 14 March 2007 12:36 Go to next message
prgiorgio
Messages: 3
Registered: March 2007
Junior Member
HOW TO obtaing this behaviour?:

i'd like to check at a line insert or update
that a field is unique if it's not null,

this doesn't work:
add constraint...
unique(field1,field2)where field2 is not null

how to solve this?
thank you in advance
Re: unique(field1,field2)where field2 is not null [message #224589 is a reply to message #224571] Wed, 14 March 2007 13:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean, it does not work?
SQL> drop table faq;

Table dropped.

SQL> create table faq
  2  ( field1 number
  3  , field2 number not null
  4  );

Table created.

SQL> alter table faq add constraint faq_uk1
  2  unique (field1, field2);

Table altered.

SQL> insert into faq values (1, 2);

1 row created.

SQL> insert into faq values (1, 2);
insert into faq values (1, 2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FAQ_UK1) violated


SQL> insert into faq values (2, 2);

1 row created.

SQL> insert into faq values (null, 1);

1 row created.

SQL> insert into faq values (null, 1);
insert into faq values (null, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FAQ_UK1) violated
Re: unique(field1,field2)where field2 is not null [message #224689 is a reply to message #224589] Thu, 15 March 2007 02:52 Go to previous messageGo to next message
prgiorgio
Messages: 3
Registered: March 2007
Junior Member
Thank you for your answer,

I'll be more precise:

i have a 600 records table,
with
field1, field2, field3,......

field2 is filled with numbers,
but on many records, where the number is not needed, it's NULL.

I need something that checks that the field2 for new/updated lines
is NULL or is a number different from any other number in the other records of the same table.

on this existing table,
>unique(field2) where field2 is not null;
is not working.

Thank you in advance
Re: unique(field1,field2)where field2 is not null [message #224691 is a reply to message #224689] Thu, 15 March 2007 02:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you understand my example?
It does exactly that.
If you want the unique index over just one column, do so and test.
If I misunderstand you, please show a copy-paste from sqlplus like I did and show where you go wrong.
Re: unique(field1,field2)where field2 is not null [message #224737 is a reply to message #224691] Thu, 15 March 2007 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that when the OP says
unique(field1,field2) where field2 is not null;
They don't mean 'field2 is a NOT NULL field', I think they mean 'I want every pair of (field1,field2) to be unique if and only if field2 is not null.

If that is the case, you can do this:
SQL> drop table faq;

Table dropped.

SQL> create table faq
  2  ( field1 number
  3  , field2 number
  4  );

Table created.

SQL> create unique index faq_unq_idx on faq (case when field2 is not null then field1 else null end,field2);

Index created.

SQL> insert into faq (field1,field2) values (1,1);

1 row created.

SQL> -- This should fail the uniqueness check
SQL> insert into faq (field1,field2) values (1,1); 
insert into faq (field1,field2) values (1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (JOHN_TEST.FAQ_UNQ_IDX) violated

SQL> insert into faq (field1,field2) values (1,null);

1 row created.

SQL> -- This should be allowed, as field2 is null
SQL> insert into faq (field1,field2) values (1,null);

1 row created.
Re: unique(field1,field2)where field2 is not null [message #224767 is a reply to message #224571] Thu, 15 March 2007 07:17 Go to previous message
prgiorgio
Messages: 3
Registered: March 2007
Junior Member
Oh ! That's GREAT! and workinig!
Thank you so much JRowbottom
(and also Franc, sure!)

That's so useful into my DB.
Previous Topic: sql fns help
Next Topic: About pragma
Goto Forum:
  


Current Time: Wed Dec 07 12:34:42 CST 2016

Total time taken to generate the page: 0.09778 seconds