Schema Comparison - Implicit vs Explicit NOT NULL Constraints

Toad for Oracle - Beta
1

These two cases produce no differences in schema comparisons (11.6 and 12.1). The end result is the same after the constraint is applied, but the DDL is still technically different since the explicit constraint is not defined on the columns. The Toad generated DDL does generate it properly with the differences. Drop the PK and they do pop up on the Colmns|nullability list.

Table DDL:
Case 1:
CREATE TABLE SPF.EE
(
BADGE_ID VARCHAR2(8) NOT NULL,
DATA_DT DATE NOT NULL,

ALTER TABLE SPF.EE ADD (
CONSTRAINT EE_PK
PRIMARY KEY
(BADGE_ID, DATA_DT)…

Case 2:
CREATE TABLE SPF.EE
(
BADGE_ID VARCHAR2(8),
DATA_DT DATE,

ALTER TABLE SPF.EE ADD (
CONSTRAINT EE_PK
PRIMARY KEY
(BADGE_ID, DATA_DT)…

2

As I recall there isn’t a way to distinguish these two cases in the data dictionary, but I’ll have another look.

3

Well, it looks like there is a difference in 11gR2, at least. I’ll see what I can do…

4

good luck with it :slight_smile:

While both NOT NULL and PRIMARY KEY have nullable=‘N’ in the dictionary, a NOT NULL PRIMARY KEY is also shown as null in DBA_TAB_COLUMNS. But it has NULL$=2 in the SYS tables

SQL> create table t1(x number primary key)

Table created.

SQL> create table t2(x number not null)

Table created.

SQL> create table t3(x number not null primary key)

Table created.

SQL> create table t4(x number )

Table created.

SQL> select table_name, column_name, nullable from user_tab_columns where table_name between ‘T1’ and ‘T9’ order by table_name

TABLE_NAME COLUMN_NAME NULLABLE

T1 X N

T2 X N

T3 X N

T4 X Y

4 rows selected.

SQL> select o.name, c.name, null$ from sys.col$ c, sys.obj$ o, sys.user$ u where u.name=‘SCOTT’ and u.user# = o.owner# and o.name between ‘T1’ and ‘T9’ and c.obj# = o.obj# and c.name=‘X’

NAME NAME_1 NULL$

T1 X 1

T2 X 1

T3 X 2

T4 X 0

5

Thanks Laurent! I know, it’s tricky, and DBA_TAB_COLUMNS is no help at all! The explicit NOT NULL constraints stick a row in DBA_CONSTRAINTS, where the ones that just ‘show up’ when created by the primary key do not.

Toad is extracting the DDL just fine in these two cases, so it seems I’ve already solved the database part of the problem. :slight_smile: It just looks like something is wrong in Toad’s schema compare that is failing to distinguish between the two. I think I’ll be able to fix it.

6

but if you check the “search condition” from dba_constraints you will not see the difference between a check and a not null (providing the text matches). Only cdef$.type# has the info, afaik

SQL> create table t2(x number not null)

Table created.

SQL> create table t5(x number check (“X” IS NOT NULL))

Table created.

SQL> select table_name, column_name, nullable from user_tab_columns where table_name between ‘T1’ and ‘T9’ order by table_name

TABLE_NAME COLUMN_NAME NULLABLE

T2 X N

T5 X Y

2 rows selected.

SQL> select o.name,con.name, cdef.type#, condition from sys.cdef$ cdef, sys.obj$ o, sys.user$ u, sys.con$ con where u.name=‘SCOTT’ and u.user# = o.owner# and o.name between ‘T1’ and ‘T9’ and cdef.obj# = o.obj# and con.con# = cdef.con#

NAME NAME_1 TYPE# CONDITION

T2 SYS_C00125644 7 “X” IS NOT NULL

T5 SYS_C00125645 1 “X” IS NOT NULL

Of course one could pretend any user who creates such a check constraint is an abuser :wink:

7

That’s a good way to check, but I can’t assume that everyone running a schema compare will be able to select from sys.cdef$. Toad looks at the search condition and takes the “abuser” approach.

8

indeed, cdef$ is not an option

Who would write something like this …

create table t6(x number not null check (“X” IS NOT NULL));

alter table t6 modify x null;

That’s good for unit testing only !

9

Right, I don’t have a lot of sympathy for people who make constraints that way! Thanks for the conversation.

10

NEVER be surprised by code people write. That’s why sites like Worse Than Failure exist!

11

OK, this is fixed for next beta.

12

Thanks and welcome to my hell! Really, I’m not making this stuff up.

Just to show some of the insanity of the named NOT NULL DISABLE NOVALIDATE constraints and PK using the wrong index …

CREATE TABLE that_schema.ref_pos

(

pos_cd VARCHAR2 (2),

type_pos_cd VARCHAR2 (1),

pwtr_type_pos_cd VARCHAR2 (1),

group_cd VARCHAR2 (6),

ref_groups_said VARCHAR2 (6),

ref_pos_said VARCHAR2 (6),

spf_disp_cd VARCHAR2 (1)

);

CREATE UNIQUE INDEX that_schema.ref_pos_pk

ON that_schema.ref_pos (pos_cd)

TABLESPACE that_schema_idx;

ALTER TABLE that_schema.ref_pos ADD (

CONSTRAINT ref_pos_ref_groups_said_nn

CHECK (ref_groups_said IS NOT NULL)

ENABLE VALIDATE);

ALTER TABLE that_schema.ref_pos ADD (

CONSTRAINT ref_pos_ref_pos_said_nn

CHECK (ref_pos_said IS NOT NULL)

DISABLE NOVALIDATE);

CREATE INDEX nwps.ref_pos___1_4_2

ON nwps.ref_pos (pos_cd, group_cd, type_pos_cd)

TABLESPACE pos_idx;

ALTER TABLE that_schema.ref_pos ADD (

CONSTRAINT ref_pos_pk

PRIMARY KEY

(pos_cd)

USING INDEX that_schema.ref_pos___1_4_2

ENABLE VALIDATE);

13

Wow!

14

Hello,

I am running into this problem on 12.6.0.53 (the problem in the original post). Is there a way to just report on the columns that truly have a difference in nullability?

Thank you very much!

15

Hi Maryanno,

In the original post, the problem was that Toad did not detect these differences. As OP commented, there really is a difference between these tables. I just tried creating the following tables and then comparing them with 12.6. Toad found the differences and synced them correctly.

If you are asking for a way to ignore this difference, then the answer is, there is none.

CREATE TABLE COMP1.EE
(
BADGE_ID VARCHAR2(8) NOT NULL,
DATA_DT DATE NOT NULL,
colx number);

ALTER TABLE COMP1.EE ADD (
CONSTRAINT EE_PK
PRIMARY KEY
(BADGE_ID, DATA_DT));

CREATE TABLE COMP2.EE
(
BADGE_ID VARCHAR2(8),
DATA_DT DATE,
colx number);

ALTER TABLE COMP2.EE ADD (
CONSTRAINT EE_PK
PRIMARY KEY
(BADGE_ID, DATA_DT));

1 Like
16

That answers it. Thank you very much!

17

Actually, I should ask you if TOAD can flag the ones that are simply a case of implicit vs explicit as opposed to ones that are 100% different. Thanks, again.

18

No, Toad does not have the ability to ignore these differences. My suggestion is to adjust your tables so that the primary key handles the not null, as in the example below. This way, you don’t have duplicate constraints in your database.

CREATE TABLE COMP2.EE
(BADGE_ID VARCHAR2(8),
DATA_DT DATE,
colx number);

ALTER TABLE COMP2.EE ADD (
CONSTRAINT EE_PK
PRIMARY KEY
(BADGE_ID, DATA_DT));

1 Like