Issue with create table script and not nulls

ken.overman · February 7, 2017, 5:25am

When I click on the Script tab (in the Schema Browser for many tables the column definitions do not include the NOT NULL, even though the Columns tab shows the columns are NOT NULL. The same is true when I highlight a table in the Schema Browser and right click, then take the Create Script option. In many cases the NOT NULL setting is not included.

I am using Toad for Oracle version 12.10.0.30.

I have gone through the TOAD Options but if there is a setting to recognize NOT NULL columns I have not found it.

JohnDorlon · February 7, 2017, 6:12am

Some NOT NULL constraints are automatically created by Oracle when you create a primary key. In these cases, our script does not also specify NOT NULL for these columns because if you do that, then you’ll end up with double not null constraints on those columns.

Try recreating the table with the script that Toad gives you and then compare the created table vs the one that you made the script from. If you spot any differences, let me know.

ken.overman · February 7, 2017, 6:38am

Thanks John.

I tested that out and here is what I found:

This is the generated script:

CREATE TABLE Dz2cpr.T_CDE_CMS_TOS

(

CDE_CMS_TOS CHAR(1 BYTE),

DSC_CMS_TOS VARCHAR2(100 BYTE)

)

TABLESPACE DATA03

LOGGING

NOCACHE

MONITORING;

CREATE UNIQUE INDEX Dz2cpr.I_CDE_CMS_TOS ON dz2cpr.T_CDE_CMS_TOS

(CDE_CMS_TOS)

LOGGING

TABLESPACE DATA03;

ALTER TABLE Dz2cpr.T_CDE_CMS_TOS ADD (

CONSTRAINT I_CDE_CMS_TOS

UNIQUE (CDE_CMS_TOS)

USING INDEX Dz2cpr.I_CDE_CMS_TOS

ENABLE VALIDATE);

The new table was created w/o the NOT NULL settings for both fields.

The file differences report shows:

Comparison of TABLE T_CDE_CMS_TOS in D@DSST1 and TABLE T_CDE_CMS_TOS in D@DSST1

2/7/2017 8:32:48 AM

L3 CDE_CMS_TOS CHAR(1 BYTE) NOT NULL,

R3 CDE_CMS_TOS CHAR(1 BYTE),

L4 DSC_CMS_TOS VARCHAR2(100 BYTE) NOT NULL

R4 DSC_CMS_TOS VARCHAR2(100 BYTE)

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Tuesday, February 07, 2017 8:13 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Issue with create table script and not nulls

RE: Issue with create table script and not nulls

Reply by John Dorlon

Some NOT NULL constraints are automatically created by Oracle when you create a primary key. In these cases, our script does not also specify NOT NULL for these columns because if you do that, then you’ll end up with double not null constraints on those columns.

Try recreating the table with the script that Toad gives you and then compare the created table vs the one that you made the script from. If you spot any differences, let me know.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · February 7, 2017, 6:47am

OK - next questions:

First, the obvious - do you have the option enabled to include check constraints? If you are getting the script from SB-Tables-Script, click the first toolbar button on the script tab, then in the dialog that appears, go to the “Tables” tab. If you are getting the script from “Generate Schema Script”, then it’s on the “Object Types” tab.
If that’s not it - do you have the script that originally created the table? I created your table here, then added not null constraints, then generated the script and it came out correctly for me. There are a few different ways to get NOT NULL on a column, so maybe your original script was a little different than what I tried here.

ken.overman · February 7, 2017, 6:59am

Thanks John. That was it. I was missing the check for the check constraints.

Thanks