Some additional info on constraints and surprises when using them

[Bert_Scalzo]

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 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”

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 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

[dbmadvig_734]

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

[dbmadvig_734]

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

[dbmadvig_734]

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

[dbmadvig_734]

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

[dbmadvig_734]

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

[dbmadvig_734]

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

[Bert_Scalzo]

Did not mean to lecture per se – but rather to inform/educate. I have been
an expert witness on multiple large legal cases where database design was an
issue and this was one of the most common unexpected negative consequences of
database design choices even though it’s well documented. Oracle allows
one to do things NOT strictly within relational sanity under the assumption
users know exactly what they’re up to and wants to do special things (i.e.
read that as circumvent things). So Oracle allows you to have a unique and/or
foreign key constraints with NULL’s which is contrary (if not simply down
right wrong) – unless you know how things work and want to do this for
some business reason. In fact try to do this thing in any good data modeling
tool and either the model check will complain or the generate DDL will yield an
error and/or warning. Databases simply consume and build what one asks for.

I think the key issue is that Oracle allows you to do atypical things without
warning – and you find out when your data is in error. Then people say doh
– I can’t believe they let me shoot myself in the foot.

So if we now know the shotgun is loaded, that the safety is not on, and that the
gun is pointed directly at our feet – maybe we’ll choose
differently.

[Bert_Scalzo]

Did not mean to lecture per se – but rather to inform/educate. I have been
an expert witness on multiple large legal cases where database design was an
issue and this was one of the most common unexpected negative consequences of
database design choices even though it’s well documented. Oracle allows
one to do things NOT strictly within relational sanity under the assumption
users know exactly what they’re up to and wants to do special things (i.e.
read that as circumvent things). So Oracle allows you to have a unique and/or
foreign key constraints with NULL’s which is contrary (if not simply down
right wrong) – unless you know how things work and want to do this for
some business reason. In fact try to do this thing in any good data modeling
tool and either the model check will complain or the generate DDL will yield an
error and/or warning. Databases simply consume and build what one asks for.

I think the key issue is that Oracle allows you to do atypical things without
warning – and you find out when your data is in error. Then people say doh
– I can’t believe they let me shoot myself in the foot.

So if we now know the shotgun is loaded, that the safety is not on, and that the
gun is pointed directly at our feet – maybe we’ll choose
differently.

[Bert_Scalzo]

Did not mean to lecture per se – but rather to inform/educate. I have been
an expert witness on multiple large legal cases where database design was an
issue and this was one of the most common unexpected negative consequences of
database design choices even though it’s well documented. Oracle allows
one to do things NOT strictly within relational sanity under the assumption
users know exactly what they’re up to and wants to do special things (i.e.
read that as circumvent things). So Oracle allows you to have a unique and/or
foreign key constraints with NULL’s which is contrary (if not simply down
right wrong) – unless you know how things work and want to do this for
some business reason. In fact try to do this thing in any good data modeling
tool and either the model check will complain or the generate DDL will yield an
error and/or warning. Databases simply consume and build what one asks for.

I think the key issue is that Oracle allows you to do atypical things without
warning – and you find out when your data is in error. Then people say doh
– I can’t believe they let me shoot myself in the foot.

So if we now know the shotgun is loaded, that the safety is not on, and that the
gun is pointed directly at our feet – maybe we’ll choose
differently.

[Bert_Scalzo]

Did not mean to lecture per se – but rather to inform/educate. I have been
an expert witness on multiple large legal cases where database design was an
issue and this was one of the most common unexpected negative consequences of
database design choices even though it’s well documented. Oracle allows
one to do things NOT strictly within relational sanity under the assumption
users know exactly what they’re up to and wants to do special things (i.e.
read that as circumvent things). So Oracle allows you to have a unique and/or
foreign key constraints with NULL’s which is contrary (if not simply down
right wrong) – unless you know how things work and want to do this for
some business reason. In fact try to do this thing in any good data modeling
tool and either the model check will complain or the generate DDL will yield an
error and/or warning. Databases simply consume and build what one asks for.

I think the key issue is that Oracle allows you to do atypical things without
warning – and you find out when your data is in error. Then people say doh
– I can’t believe they let me shoot myself in the foot.

So if we now know the shotgun is loaded, that the safety is not on, and that the
gun is pointed directly at our feet – maybe we’ll choose
differently.

[Bert_Scalzo]

Did not mean to lecture per se – but rather to inform/educate. I have been
an expert witness on multiple large legal cases where database design was an
issue and this was one of the most common unexpected negative consequences of
database design choices even though it’s well documented. Oracle allows
one to do things NOT strictly within relational sanity under the assumption
users know exactly what they’re up to and wants to do special things (i.e.
read that as circumvent things). So Oracle allows you to have a unique and/or
foreign key constraints with NULL’s which is contrary (if not simply down
right wrong) – unless you know how things work and want to do this for
some business reason. In fact try to do this thing in any good data modeling
tool and either the model check will complain or the generate DDL will yield an
error and/or warning. Databases simply consume and build what one asks for.

I think the key issue is that Oracle allows you to do atypical things without
warning – and you find out when your data is in error. Then people say doh
– I can’t believe they let me shoot myself in the foot.

So if we now know the shotgun is loaded, that the safety is not on, and that the
gun is pointed directly at our feet – maybe we’ll choose
differently.

[Bert_Scalzo]

Did not mean to lecture per se – but rather to inform/educate. I have been
an expert witness on multiple large legal cases where database design was an
issue and this was one of the most common unexpected negative consequences of
database design choices even though it’s well documented. Oracle allows
one to do things NOT strictly within relational sanity under the assumption
users know exactly what they’re up to and wants to do special things (i.e.
read that as circumvent things). So Oracle allows you to have a unique and/or
foreign key constraints with NULL’s which is contrary (if not simply down
right wrong) – unless you know how things work and want to do this for
some business reason. In fact try to do this thing in any good data modeling
tool and either the model check will complain or the generate DDL will yield an
error and/or warning. Databases simply consume and build what one asks for.

I think the key issue is that Oracle allows you to do atypical things without
warning – and you find out when your data is in error. Then people say doh
– I can’t believe they let me shoot myself in the foot.

So if we now know the shotgun is loaded, that the safety is not on, and that the
gun is pointed directly at our feet – maybe we’ll choose
differently.

[Guest]

Did not mean to lecture per se – but rather to inform/educate

For some people, the two are one and the same!

Roger S.

[dbmadvig_734]

Sorry if lecture was the wrong word. I meant the kind of “talk” you
might get at a workshop in which the presenter (lecturer?) is letting you in on
some really good stuff.

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

Thanks yet again,

Dan

Daniel B Madvig
Campus Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

[dbmadvig_734]

Sorry if lecture was the wrong word. I meant the kind of “talk” you
might get at a workshop in which the presenter (lecturer?) is letting you in on
some really good stuff.

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

Thanks yet again,

Dan

Daniel B Madvig
Campus Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

[dbmadvig_734]

Sorry if lecture was the wrong word. I meant the kind of “talk” you
might get at a workshop in which the presenter (lecturer?) is letting you in on
some really good stuff.

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

Thanks yet again,

Dan

Daniel B Madvig
Campus Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

[dbmadvig_734]

Sorry if lecture was the wrong word. I meant the kind of “talk” you
might get at a workshop in which the presenter (lecturer?) is letting you in on
some really good stuff.

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

Thanks yet again,

Dan

Daniel B Madvig
Campus Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

[dbmadvig_734]

Sorry if lecture was the wrong word. I meant the kind of “talk” you
might get at a workshop in which the presenter (lecturer?) is letting you in on
some really good stuff.

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

Thanks yet again,

Dan

Daniel B Madvig
Campus Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

[dbmadvig_734]

Sorry if lecture was the wrong word. I meant the kind of “talk” you
might get at a workshop in which the presenter (lecturer?) is letting you in on
some really good stuff.

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

Thanks yet again,

Dan

Daniel B Madvig
Campus Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323