Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

I have to contradict here. Any 'default' value just to tell 'it is bogus' is far from the spirit of relational structures. Even the usage of NULL at all can be avoided. The big problem here is (as Lex de Haan described) is you can not distinct, if NULL means 'Inapplicable', 'Not Yet Applicable', or 'Nice to know' (aka 'unimportant').
I try to show you some escape here.
Let's try the original table & inserts:

> create table test(id int primary key, pia int, ua int, data date, ver int);
> create unique index unik on test(pia int, ua , data , ver )
> insert into test values (1,37,76,null,1);
> insert into test values (2,37,76,null,1);

I would like to translate it into:

create table test(id int primary key, pia int, ua int, ver int); create unique index unik on test(pia int, ua , ver );

create table test_data(id int primary key, test_id int REFERENCES test(id), data date );
create unique index td_uink on test_data(test_id);

insert into test values (1,37,76,1);
insert into test values (2,37,76,1);

so no NULL is needed at all - in best/worst case it's VISIBLE at the outer join of test and test_data.

I'm quite sure this is a better solution than pollute the system first with any bogus information, just to try to help out with histograms afterwards?

If someone complains about more IOs because of the 2 tables, I just can recommend to check the logic behind CLUSTERS.

hth
 Martin

On Fri, Mar 30, 2012 at 13:27, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> You're welcome.
>
> Often, midnight or one second past midnight on Jan. 1, 1970 can be well
> known as the value for "this is not a real date" in honor of the epoch time
> of UNIX. There are other reasonable choices as well, particularly if you are
> doing history or something like that.  In some cases having a special value
> is not useful, but it does prevent row length change when it is updated to
> the "real date" that may later be appearing. And you can search for it with
> an equals predicate on an index if you're looking for dates that haven't
> been processed yet.
>
> If you're not worried about row length change (or if there is a positive
> trade-off versus having a lower pctfree) you can use defaults values for
> rows inserted without values for particular columns. With a default chosen
> that is unlikely to by a real value, you can check the data dictionary for
> this value to firewall your application software instead of relying on a
> presumed constant (like 1/1/1970.)
>
> Often a little bit of planning of this nature in the data model design
> considered against the planned data flow through an application can make
> implementing the system easier, and you may indeed end up with a system
> containing few nulls. One notable exception is having a final status value
> of null to mean "DONE" on an indexed transaction control column, which takes
> advantage of the fact that all null index entries do not appear in the index
> for routine Oracle indexes (circling back to the original topic).

--
http://www.freelists.org/webpage/oracle-l