Named NOT NULL's obtain Anonymous twins

[MarkP]

Hi Marco,

I'm sure that you must have this buglet somewhere on your hit-list, but just in case it has been missed ...

'View SQL' shows me

create table APPS_CISC.SHIPTOXREF
(
RECID NUMBER not null,
CUSTSHRTNAME VARCHAR2(10) not null,
SHIPTO VARCHAR2(30) not null,
SHIPTONAME VARCHAR2(30) not null,
CUSTCTRY VARCHAR2(6) not null,
CUSTBILLTO NUMBER(6) not null,
CUSTSHIPTO NUMBER(3) not null
)

.
. followed by ...
.

alter table APPS_CISC.SHIPTOXREF
add constraint SHIPTOXREF_CUSTCTRYBILLSHIP_FK foreign key (CUSTCTRY,CUSTBILLTO,CUSTSHIPTO)
references CSAP.CUSTOMER_SHIPTO_MASTER (CUSTCTRY,CUSTBILLTO,CUSTSHIPTO);

... and so on for each of the original, named constraints.

When this SQL is replayed (OK, mindlessly

) in a Command Window, the result is that the NOT NULL constraints are duplicated -- for each named constraint there is a system-named (anonymous) twin.

Would you mind checking, please, that you have on your list of enhancements the removal of those anonymous 'not null' constraints from the CREATE TABLE?

Thank you for considering this.

Best Regards, Mark.

 

[Marco Kalter]

I can't really reproduce this. Can you send me the complete table definition?

------------------
Marco Kalter
Allround Automations

 

[MarkP]

Thank you for looking, Marco.

Yes, I've just checked, the fault seems to depend on the way that the constraints have been defined. (BTW: We're using Orrible 8.1.7, and PL/SQL Dev 5.1.2.687)

The following steps demonstrate the problem.

First, create a table as follows

CREATE TABLE APPS_CISC.TESTNOTNULL (
COL1 VARCHAR2(10)
, COL2 VARCHAR2(10) CONSTRAINT TESTNOTNULL_COL2_NN NOT NULL
, COL3 VARCHAR2(10) CONSTRAINT TESTNOTNULL_COL3_NN NOT NULL
, COL4 VARCHAR2(10) CONSTRAINT TESTNOTNULL_COL4_NN NOT NULL
, COL5 VARCHAR2(10) CONSTRAINT TESTNOTNULL_COL5_NN NOT NULL
, CONSTRAINT TESTNOTNULL_PK PRIMARY KEY (COL1) USING INDEX TABLESPACE DAT
) TABLESPACE DAT PCTFREE 10 PCTUSED 80

Then 'View' and 'View SQL' to obtain ...

-- Create table
create table APPS_CISC.TESTNOTNULL
(
  COL1 VARCHAR2(10) not null,
  COL2 VARCHAR2(10) not null,
  COL3 VARCHAR2(10) not null,
  COL4 VARCHAR2(10) not null,
  COL5 VARCHAR2(10) not null
)
tablespace DAT
  pctfree 10
  pctused 80
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table APPS_CISC.TESTNOTNULL
  add constraint TESTNOTNULL_PK primary key (COL1)
  using index
  tablespace DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate check constraints
alter table APPS_CISC.TESTNOTNULL
  add constraint TESTNOTNULL_COL2_NN
  check ("COL2" IS NOT NULL);
alter table APPS_CISC.TESTNOTNULL
  add constraint TESTNOTNULL_COL3_NN
  check ("COL3" IS NOT NULL);
alter table APPS_CISC.TESTNOTNULL
  add constraint TESTNOTNULL_COL4_NN
  check ("COL4" IS NOT NULL);
alter table APPS_CISC.TESTNOTNULL
  add constraint TESTNOTNULL_COL5_NN
  check ("COL5" IS NOT NULL);

Notice how those 'anonymous' NOT NULLS have appeared in the CREATE TABLE statement for the PRIMARY KEY columns as well as for the CHECK NOT NULL'd columns.

The fault does not appear if the table is created as follows:

CREATE TABLE APPS_CISC.TESTNOTNULL2 (
COL1 VARCHAR2(10)
, COL2 VARCHAR2(10)
, COL3 VARCHAR2(10)
, COL4 VARCHAR2(10)
, COL5 VARCHAR2(10)
, CONSTRAINT TESTNOTNULL2_PK PRIMARY KEY (COL1) USING INDEX TABLESPACE DAT
) TABLESPACE DAT PCTFREE 10 PCTUSED 80
;
;ALTER TABLE APPS_CISC.TESTNOTNULL2 ADD CONSTRAINT TESTNOTNULL2_COL2_NN CHECK (COL2 IS NOT NULL)
;ALTER TABLE APPS_CISC.TESTNOTNULL2 ADD CONSTRAINT TESTNOTNULL2_COL3_NN CHECK (COL3 IS NOT NULL)
;ALTER TABLE APPS_CISC.TESTNOTNULL2 ADD CONSTRAINT TESTNOTNULL2_COL4_NN CHECK (COL4 IS NOT NULL)
;ALTER TABLE APPS_CISC.TESTNOTNULL2 ADD CONSTRAINT TESTNOTNULL2_COL5_NN CHECK (COL5 IS NOT NULL)
;

... well, it hasn't completely disappeared ... Just look at COL1's definition in the CREATE TABLE statement generated by 'View SQL':

-- Create table
create table APPS_CISC.TESTNOTNULL2
(
  COL1 VARCHAR2(10) not null,
  COL2 VARCHAR2(10),
  COL3 VARCHAR2(10),
  COL4 VARCHAR2(10),
  COL5 VARCHAR2(10)
)
tablespace DAT
  pctfree 10
  pctused 80
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table APPS_CISC.TESTNOTNULL2
  add constraint TESTNOTNULL2_PK primary key (COL1)
  using index
  tablespace DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate check constraints
alter table APPS_CISC.TESTNOTNULL2
  add constraint TESTNOTNULL2_COL2_NN
  check (COL2 IS NOT NULL);
alter table APPS_CISC.TESTNOTNULL2
  add constraint TESTNOTNULL2_COL3_NN
  check (COL3 IS NOT NULL);
alter table APPS_CISC.TESTNOTNULL2
  add constraint TESTNOTNULL2_COL4_NN
  check (COL4 IS NOT NULL);
alter table APPS_CISC.TESTNOTNULL2
  add constraint TESTNOTNULL2_COL5_NN
  check (COL5 IS NOT NULL);

I hope that this helps. OK, perhaps I should have email'd the code

.

Best Regards, Mark.

 

[Marco Kalter]

That is indeed not correct. We'll fix it.

------------------
Marco Kalter
Allround Automations

 

[Duke Ganote]

I'm sorry, but this:

alter table TESTNOTNULL2 add constraint TESTNOTNULL2_COL5_NN check (COL5 IS NOT NULL);

is NOT the same as:

alter table TESTNOTNULL2 modify COL5 constraint TESTNOTNULL2_COL5_NN NOT NULL;

The former is a simple user-defined check constraint that happened to check for non-nullity. The latter is a NOT NULL constraint (a subtype of check constraint) recognized by the Oracle cost based optimizer. A describe of the table will show the difference.

See also http://asktom.oracle.com/pls/ask/f?p=4950:61:1066497874484516242:::61_ID:14688015228501#19679633683960

I prefer using the prettier "SQL Inserts" option instead of "Oracle Export", but I'm annoyed at having to correct the results for my named not null constraints. Am I missing something?

 

[Marco Kalter]

We'll see if we can treat a NOT NULL constraint different than a (COL IS NOT NULL) check constraint. I assume the dictionary will allow us to recognize them.