CHECK constraints are not displayed in the Script tab for a table

Toad for Oracle
1

Hi

I have noticed that CHECK constraints are not listed in the Script tab for a table in Schema Browser.

Is this omission deliberate, a bug, or is there an option somewhere that I am missing?

Thanks

Stuart

2

It’s working here. Click the first toolbar button on the script tab. In the dialog that appears, go to the “Tables” tab. Is the “Check constraints” box checked?

3

A little more investigation and now I understand.

The CHECK constraints are listed in the script except for the NOT NULL constraints. Whereas this would be superfluous as the columns themselves are marked as NOT NULL, we have some schemas where the NOT NULL constraints have been explicitly named.

In this case, a schema has been cloned from another using the script and then the checker fails as the naming convention is lost.

No biggy, just an interesting one to understand.

Thanks

4

Did Toad lose the naming convention or did that happen by some other process? If Toad lost them, it’s a bug and I’d like to fix it…but named not null constraints are working correctly here so I’d need some more details to reproduce it.

For example I am seeing this…

CREATE TABLE DBA_OBJS_COPY
(
OWNER VARCHAR2(128 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE) CONSTRAINT DBA_OBJS_COPY_NN_OBJ_NAME NOT NULL,
SUBOBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_ID NUMBER,

Thanks.

5

Something a little odd going on…

I cannot reproduce this with any table I create, however I try to create it. TOAD displays the script as expected.

The table(s) in question have a strange configuration, each NOT NULL constraint is defined twice. There is one named SYS_… with text “COLUMN” IS NOT NULL and another named NN_COLUMN_NAME with text COLUMN IS NOT NULL.

Not sure how this has occurred, maybe through some upgrade process??? These are old tables in a schema that has seen many Oracle versions.

Thanks.

6

Oh, yeah duplicate constraints. I’ve seen that happen before.

If a script-generating tool isn’t careful, it’s easy to do this with primary key columns. Oracle automatically the columns of a PK makes them NOT NULL (DBA_TAB_COLUMNS shows that, but it doesn’t create a separate check constraint in DBA_CONSTRAINTS) but if you create the table like below, then you’ll end up with an unnecessary check constraint. This doesn’t sound exactly like your case but maybe something similar caused it.

create table blah
(col1 number not null,
col2 number,
col3 varchar2(30));

alter table blah
add constraint blah_pk
primary key (col1);

I’d say you can probably just drop the system-named constraints that are duplicates of user-named constraints.