Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: April 28, 2017 - 5:22 am UTC

Last updated: May 04, 2017 - 1:57 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi,

In Below Document i found this Text 'Oracle considers two nulls to be equal when evaluating a DECODE function. Please refer to DECODE for syntax and additional information.

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.'

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm

I know Decode Function but i am not Familiar with this Concept 'COMPOUND KEYS', i googled it but i could not find any source for it.
Would Explain about that concept also Give some Reference for that if Possible.



Tank you in Advance.

and Connor said...

A compound key is a key comprising more than 1 attribute (column). In this case, uniqueness is defined by all of the non-null values, hence from that perspective, multiple null values could be viewed as "equivalent".

For example

SQL> create table t ( x int, y int, z int );

Table created.

SQL>
SQL> create unique index ix on t ( x,y);

Index created.

SQL>
SQL> insert into t values (1,null,10);

1 row created.

SQL> insert into t values (1,null,20);
insert into t values (1,null,20)
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.IX) violated


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Difference

santhoshreddy podduturi, May 03, 2017 - 10:46 am UTC

Is there any difference between compound and composite keys?
Connor McDonald
May 04, 2017 - 1:57 am UTC

People often use the terms interchangably, but there is a subtle difference

Reproduced by https://en.wikipedia.org/wiki/Compound_key

"In database design, a compound key is a set of superkeys that is not minimal.
A composite key is a set that contains a compound key and at least one attribute that is not a superkey."

And a simple example may assist here:

Let's I have car ownership, my primary key might be:

PERSON_ID
CAR_ID

Notice that each of these is a key in its own right (to persons and cars). So it's a compound key. But if I started handling multiple ownership of the same car by the same person, the key might be:

PERSON_ID
CAR_ID
START_OF_OWNERSHIP_DATE

This is now a set of attributes used to uniquely identify the row (ie, START_OF_OWNERSHIP_DATE is not a key to anything), so its a composite key