Databases
StackOverflow2013
Postsdbo
POHow can I constrain multiple columns to prevent duplicates, but ignore null values?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I constrain multiple columns to prevent duplicates, but ignore null values?
    primarykey
    data
    text
    <p>Here's a little experiment I ran in an Oracle database (10g). Aside from (Oracle's) implementation convenience, I can't figure out why some insertions are accepted and others rejected.</p> <pre><code>create table sandbox(a number(10,0), b number(10,0)); create unique index sandbox_idx on sandbox(a,b); insert into sandbox values (1,1); -- accepted insert into sandbox values (1,2); -- accepted insert into sandbox values (1,1); -- rejected insert into sandbox values (1,null); -- accepted insert into sandbox values (2,null); -- accepted insert into sandbox values (1,null); -- rejected insert into sandbox values (null,1); -- accepted insert into sandbox values (null,2); -- accepted insert into sandbox values (null,1); -- rejected insert into sandbox values (null,null); -- accepted insert into sandbox values (null,null); -- accepted </code></pre> <p>Assuming that it makes sense to occasionally have some rows with some column values unknown, I can think of two possible use cases involving preventing duplicates:<br> 1. I want to reject duplicates, but accept when any constrained column's value is unknown.<br> 2. I want to reject duplicates, even in cases when a constrained column's value is unknown. </p> <p>Apparently Oracle implements something different though:<br> 3. Reject duplicates, but accept (only) when <em>all</em> constrained column values are unknown. </p> <p>I can think of ways to make use of Oracle's implementation to get to use case (2) -- for example, have a special value for "unknown", and make the columns non-nullable. But I can't figure out how to get to use case (1).</p> <p>In other words, how can I get Oracle to act like this?</p> <pre><code>create table sandbox(a number(10,0), b number(10,0)); create unique index sandbox_idx on sandbox(a,b); insert into sandbox values (1,1); -- accepted insert into sandbox values (1,2); -- accepted insert into sandbox values (1,1); -- rejected insert into sandbox values (1,null); -- accepted insert into sandbox values (2,null); -- accepted insert into sandbox values (1,null); -- accepted insert into sandbox values (null,1); -- accepted insert into sandbox values (null,2); -- accepted insert into sandbox values (null,1); -- accepted insert into sandbox values (null,null); -- accepted insert into sandbox values (null,null); -- accepted </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USChris
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I constrain multiple columns to prevent duplicates, but ignore null values?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I constrain multiple columns to prevent duplicates, but ignore null values?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow can I constrain multiple columns to prevent duplicates, but ignore null values?
      1. USorbfish
      1. VTFavorite
    1. COPerfect example of a good question (plus it's one I needed answered!)
      singulars
      1. POHow can I constrain multiple columns to prevent duplicates, but ignore null values?
      1. USorbfish
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload