RE: Some additional info on constraints and surprises when using them

Bert,

Thanks much for the very informative "lecture".

 

What is the significance of the two blue arrows in Figure 21-4?

 

I find it counter-intuitive (a surprise?) that in Figure 21-6 Oracle
would allow a null to be inserted. If it's not in the parent table, it
ought not be allowed in a child table. My $0.02.

 

AARRGG!!!  And have a great weekend!

 

Dan

 

Sent from my Black & Decker Toaster Oven

 

From: [email protected] [mailto:[email protected]] On Behalf Of
Bert Scalzo
Sent: Friday, February 24, 2012 5:44 PM
To: [email protected]; [email protected]
Subject: [toad] Some additional info on constraints and surprises when
using them

 

  

For those whose interest and/or ire may have been sparked by the recent
unique constraint discussion between John and I regarding the data
compare/sync using unique keys and what unique keys actually do (or do
not do) - I thought I'd copy some Oracle docs on the topic for those who
may not have liked the answer about unique keys.

 

The key sentence in the entire section for unique keys is this: A null
in a column (or in all columns of a composite UNIQUE key) always
satisfies a UNIQUE key constraint.

 

Note too that foreign keys follow a similar unobvious rule: If any
column of a composite foreign key is null, then the non-null portions of
the key do not have to match any corresponding portion of a parent key.

 

I've been teaching Oracle since the early 90's and always made sure to
show people this section from the concepts guide - because the way
constraints work is not always what people expect. So this material is
handy to remember when defining keys. Note that there are simply
pictures that clearly relate this behavior (see big arrows below) J

 


UNIQUE Key Integrity Constraints


A UNIQUE key integrity constraint requires that every value in a column
or set of columns (key) be unique-that is, no two rows of a table have
duplicate values in a specified column or set of columns.

For example, in Figure 21-3
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i379
8>  a UNIQUE key constraint is defined on the DNAME column of the dept
table to disallow rows with duplicate department names.

Figure 21-3 A UNIQUE Key Constraint

 
Description of "Figure 21-3 A UNIQUE Key Constraint"
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/img_text/cncpt053
.htm> 


Unique Keys


The columns included in the definition of the UNIQUE key constraint are
called the unique key. Unique key is often incorrectly used as a synonym
for the terms UNIQUE key constraint or UNIQUE index. However, note that
key refers only to the column or set of columns used in the definition
of the integrity constraint.

If the UNIQUE key consists of more than one column, then that group of
columns is said to be a composite unique key. For example, in Figure
21-4
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i376
8>  the customer table has a UNIQUE key constraint defined on the
composite unique key: the area and phone columns.

Figure 21-4 A Composite UNIQUE Key Constraint

  
Description of "Figure 21-4 A Composite UNIQUE Key Constraint"
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/img_text/cncpt054
.htm> 

This UNIQUE key constraint lets you enter an area code and telephone
number any number of times, but the combination of a given area code and
given telephone number cannot be duplicated in the table. This
eliminates unintentional duplication of a telephone number.


UNIQUE Key Constraints and Indexes


Oracle enforces unique integrity constraints with indexes. For example,
in Figure 21-4
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i376
8> , Oracle enforces the UNIQUE key constraint by implicitly creating a
unique index on the composite unique key. Therefore, composite UNIQUE
key constraints have the same limitations imposed on composite indexes:
up to 32 columns can constitute a composite unique key.

Note:

If compatibility is set to Oracle9i or higher, then the total size in
bytes of a key value can be almost as large as a full block. In previous
releases key size could not exceed approximately half the associated
database's block size.

If a usable index exists when a unique key constraint is created, the
constraint uses that index rather than implicitly creating a new one.


Combine UNIQUE Key and NOT NULL Integrity Constraints


In Figure 21-3
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i379
8>  and Figure 21-4
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i376
8> , UNIQUE key constraints allow the input of nulls unless you also
define NOT NULL constraints for the same columns. In fact, any number of
rows can include nulls for columns without NOT NULL constraints because
nulls are not considered equal to anything. A null in a column (or in
all columns of a composite UNIQUE key) always satisfies a UNIQUE key
constraint.

Columns with both unique keys and NOT NULL integrity constraints are
common. This combination forces the user to enter values in the unique
key and also eliminates the possibility that any new row's data will
ever conflict with an existing row's data.

Note:

Because of the search mechanism for UNIQUE constraints on more than one
column, you cannot have identical values in the non-null columns of a
partially null composite UNIQUE key constraint.


PRIMARY KEY Integrity Constraints


Each table in the database can have at most one PRIMARY KEY constraint.
The values in the group of one or more columns subject to this
constraint constitute the unique identifier of the row. In effect, each
row is named by its primary key values.

The Oracle implementation of the PRIMARY KEY integrity constraint
guarantees that both of the following are true:

*         No two rows of a table have duplicate values in the specified
column or set of columns.

*         The primary key columns do not allow nulls. That is, a value
must exist for the primary key columns in each row.


Primary Keys


The columns included in the definition of a table's PRIMARY KEY
integrity constraint are called the primary key. Although it is not
required, every table should have a primary key so that:

*         Each row in the table can be uniquely identified

*         No duplicate rows exist in the table

Figure 21-5
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i377
1>  illustrates a PRIMARY KEY constraint in the dept table and examples
of rows that violate the constraint.

Figure 21-5 A Primary Key Constraint

 
Description of "Figure 21-5 A Primary Key Constraint"
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/img_text/cncpt055
.htm> 


PRIMARY KEY Constraints and Indexes


Oracle enforces all PRIMARY KEY constraints using indexes. In Figure
21-5
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i377
1> , the primary key constraint created for the deptno column is
enforced by the implicit creation of:

*         A unique index on that column

*         A NOT NULL constraint for that column

Composite primary key constraints are limited to 32 columns, which is
the same limitation imposed on composite indexes. The name of the index
is the same as the name of the constraint. Also, you can specify the
storage options for the index by including the ENABLE clause in the
CREATE TABLE or ALTER TABLE statement used to create the constraint. If
a usable index exists when a primary key constraint is created, then the
primary key constraint uses that index rather than implicitly creating a
new one.


Referential Integrity Constraints


Different tables in a relational database can be related by common
columns, and the rules that govern the relationship of the columns must
be maintained. Referential integrity rules guarantee that these
relationships are preserved.

The following terms are associated with referential integrity
constraints.

Term

Definition

Foreign key

The column or set of columns included in the definition of the
referential integrity constraint that reference a referenced key.

Referenced key

The unique key or primary key of the same or different table that is
referenced by a foreign key.

Dependent or child table

The table that includes the foreign key. Therefore, it is the table that
is dependent on the values present in the referenced unique or primary
key.

Referenced or parent table

The table that is referenced by the child table's foreign key. It is
this table's referenced key that determines whether specific inserts or
updates are allowed in the child table.

 

A referential integrity constraint requires that for each row of a
table, the value in the foreign key matches a value in a parent key.

Figure 21-6
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i377
3>  shows a foreign key defined on the deptno column of the emp table.
It guarantees that every value in this column must match a value in the
primary key of the dept table (also the deptno column). Therefore, no
erroneous department numbers can exist in the deptno column of the emp
table.

Foreign keys can be defined as multiple columns. However, a composite
foreign key must reference a composite primary or unique key with the
same number of columns and the same datatypes. Because composite primary
and unique keys are limited to 32 columns, a composite foreign key is
also limited to 32 columns.

Figure 21-6 Referential Integrity Constraints

 
Description of "Figure 21-6 Referential Integrity Constraints"
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/img_text/cncpt056
.htm> 


Self-Referential Integrity Constraints


Another type of referential integrity constraint, shown in Figure 21-7
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i377
6> , is called a self-referential integrity constraint. This type of
foreign key references a parent key in the same table.

In Figure 21-7
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i377
6> , the referential integrity constraint ensures that every value in
the mgr column of the emp table corresponds to a value that currently
exists in the empno column of the same table, but not necessarily in the
same row, because every manager must also be an employee. This integrity
constraint eliminates the possibility of erroneous employee numbers in
the mgr column.

Figure 21-7 Single Table Referential Constraints

 
Description of "Figure 21-7 Single Table Referential Constraints "
<http://docs.oracle.com/cd/B19306_01/server.102/b14220/img_text/cncpt057
.htm> 


Nulls and Foreign Keys


The relational model permits the value of foreign keys either to match
the referenced primary or unique key value, or be null. If any column of
a composite foreign key is null, then the non-null portions of the key
do not have to match any corresponding portion of a parent key.