Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Don.

Asked: May 19, 2017 - 3:43 pm UTC

Last updated: March 03, 2018 - 5:42 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

How can I name a "not null" constraint that sets the NULLABLE flag in DBA/USER_TAB_COLUMNS?

If a column is nullable and I add a check constraint with a name that meets our company standards, the column still shows as NULLABLE. If I alter the table and modify the column to not null, the constraint is named something like "SYS_C002822781." Since we have many databases and schemas with the same DDL, the system generated names never match (which is a goal of a database clean up and synch project).

I've been unable to discover a way to add a constraint name to an instruction like "alter table FOO modify BAR not null;"

Any help is greatly appreciated.

Thanx in advance,
Don Simpson



with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_E0JLEPYFVURA8F1P65XGLXSWT.html

and Connor said...

Here's how you can do it

SQL> create table t ( x int );

Table created.

SQL>
SQL> alter table t add constraint my_named_not_null check ( x is not null );

Table altered.

SQL>
SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      NUMBER(38)


but notice that it is subtly different to the standard not null constraint (see how it doesn't appear in the desc command).


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

modify statement is possible

Frank, May 22, 2017 - 7:12 pm UTC

A modify statements can also be used:

SQL> create table t (x int);

Table created.

SQL> alter table t modify x constraint my_named_not_null not null;

Table altered.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                         NOT NULL NUMBER(38)

SQL> select table_name, column_name , nullable from dba_tab_columns where table_name = 'T' and column_name = 'X';

TABLE_NAME                     COLUMN_NAME                    N
------------------------------ ------------------------------ -
T                              X                              N

SQL> select constraint_name, table_name from dba_constraints where table_name = 'T';

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
MY_NAMED_NOT_NULL              T

SQL>

Connor McDonald
May 23, 2017 - 1:19 am UTC

Nice input.

Don Simpson, May 25, 2017 - 3:57 pm UTC

The problem is that the NULLABLE flag in DBA/USER_TAB_COLUMNS is "Y"

This seems to be the syntax I was looking for:

alter table t modify x constraint my_named_not_null not null;

Connor McDonald
May 26, 2017 - 1:12 am UTC

I've covered it more detail here


Don Simpson, May 26, 2017 - 3:05 pm UTC

That video is outstanding. Nothing beats a clear, concise, step-by-step analysis and presentation.

Connor McDonald
May 27, 2017 - 6:46 am UTC

Thanks for the feedback.

NOT NULL on Views

Rajeshwaran, Jeyabal, December 10, 2017 - 2:45 am UTC

Team,

Is it possible to get a NOT NULL constraint over a view, that has union all in place?
See below, we have column Y as not null on a view without union all.
with union all we lost the not null on column Y - please advise.
demo@ORA11G> set linesize 71
demo@ORA11G> create table t(x int,y varchar2(30) NOT NULL);

Table created.

demo@ORA11G>
demo@ORA11G> insert into t(x,y)
  2  select object_id,object_name
  3  from all_objects
  4  where rownum <=5;

5 rows created.

demo@ORA11G> commit;

Commit complete.

demo@ORA11G> create or replace view v
  2  as
  3  select x,y from t;

View created.

demo@ORA11G>
demo@ORA11G> desc v
 Name                                Null?    Type
 ----------------------------------- -------- ------------------
 X                                            NUMBER(38)
 Y                                   NOT NULL VARCHAR2(30)

demo@ORA11G> create or replace view v
  2  as
  3  select x,y from t
  4  union all
  5  select x,y from t ;

View created.

demo@ORA11G>
demo@ORA11G> desc v
 Name                                Null?    Type
 ----------------------------------- -------- ------------------
 X                                            NUMBER(38)
 Y                                            VARCHAR2(30)

demo@ORA11G>

Chris Saxon
December 12, 2017 - 10:03 am UTC

You can't create not null constraints on views:

You cannot specify NULL or NOT NULL in a view constraint.

https://docs.oracle.com/database/122/SQLRF/constraint.htm#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE__GUID-DDBE167E-2BF4-41FA-8A7B-56DD5C38B410

In the first example describe is able to see the constraint on the base table because you only query one. But in the general case with union all, there's no guarantee that the resulting value is not null.

NOT NULL on Views

Rajeshwaran Jeyabal, December 12, 2017 - 11:39 am UTC

Thanks Chirs that helps.

Could you upload the slides/deck presented by AskTom team in UKOUG/Sangam 17 conferences?
Connor McDonald
December 13, 2017 - 1:51 am UTC

They'll be coming soon

constrained not validated after redef

Bernd Eckenfels, March 01, 2018 - 4:07 am UTC

Thanks Connor for the Video.

Any idea why the online redefinition manual seem to not mention that PKs and Null constraints are not enabled/validated? (1089860.1 suggests intentional?)

Should be documented when and how they need to be validaed andwhat impact. (I would assume you can enable them before doing FINISH?)

This lists them:

SELECT l.OWNER,l.TABLE_NAME,l.COLUMN_NAME,l.CONSTRAINT_NAME,s.STATUS,s.VALIDATED,s.SEARCH_CONDITION
from USER_CONS_COLUMNS l,USER_CONSTRAINTS s
where
  l.CONSTRAINT_NAME=s.CONSTRAINT_NAME and l.OWNER=s.OWNER and l.TABLE_NAME = s.TABLE_NAME and
  (s.STATUS!='ENABLED' or s.VALIDATED!='VALIDATED');


and then using the constrained name to enable and validate them:

ALTER TABLE t ENABLE VALIDATE CONSTRAINT c;

Connor McDonald
March 03, 2018 - 5:42 am UTC

I think we've resolved that now

SQL> CREATE TABLE redef_tab (
  2    id           NUMBER,
  3    description  VARCHAR2(50),
  4    nn_col      int not null,
  5    nn2_col     int,
  6    CONSTRAINT redef_tab_pk PRIMARY KEY (id),
  7    constraint redef_tab_ck check ( nn2_col is not null )
  8  );

Table created.

SQL>
SQL> desc redef_tab
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER
 DESCRIPTION                                                                      VARCHAR2(50)
 NN_COL                                                                  NOT NULL NUMBER(38)
 NN2_COL                                                                          NUMBER(38)

SQL> select * from user_constraints
  2  where table_name = 'REDEF_TAB'
  3  @pr
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : SYS_C0061374
CONSTRAINT_TYPE               : C
TABLE_NAME                    : REDEF_TAB
SEARCH_CONDITION              : "NN_COL" IS NOT NULL
SEARCH_CONDITION_VC           : "NN_COL" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 03-MAR-18
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 0
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : REDEF_TAB_CK
CONSTRAINT_TYPE               : C
TABLE_NAME                    : REDEF_TAB
SEARCH_CONDITION              :  nn2_col is not null
SEARCH_CONDITION_VC           :  nn2_col is not null
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 03-MAR-18
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 0
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : REDEF_TAB_PK
CONSTRAINT_TYPE               : P
TABLE_NAME                    : REDEF_TAB
SEARCH_CONDITION              :
SEARCH_CONDITION_VC           :
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 03-MAR-18
INDEX_OWNER                   : MCDONAC
INDEX_NAME                    : REDEF_TAB_PK
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 0

PL/SQL procedure successfully completed.


SQL>
SQL>
SQL> insert into redef_tab values (1,'x',11,21);

1 row created.

SQL> insert into redef_tab values (2,'x',12,22);

1 row created.

SQL> insert into redef_tab values (3,'x',13,23);

1 row created.

SQL>
SQL> EXEC DBMS_REDEFINITION.can_redef_table(user, 'REDEF_TAB');

PL/SQL procedure successfully completed.

SQL>
SQL> CREATE TABLE redef_tab2 (
  2    id           NUMBER,
  3    description  VARCHAR2(50),
  4    nn_col      int,
  5    nn2_col     int
  6  );

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2        error_count pls_integer := 0;
  3  BEGIN
  4       DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(user,
  5             'REDEF_TAB',
  6             'REDEF_TAB2',
  7             dbms_redefinition.cons_orig_params ,
  8             TRUE,
  9             TRUE,
 10             TRUE,
 11             FALSE,
 12             error_count);
 13         DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
 14  END;
 15  /
errors := 0

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL>
SQL> desc redef_tab
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER
 DESCRIPTION                                                                      VARCHAR2(50)
 NN_COL                                                                  NOT NULL NUMBER(38)
 NN2_COL                                                                          NUMBER(38)

SQL> select * from user_constraints
  2  where table_name = 'REDEF_TAB'
  3  @pr
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : REDEF_TAB_CK
CONSTRAINT_TYPE               : C
TABLE_NAME                    : REDEF_TAB
SEARCH_CONDITION              :  nn2_col is not null
SEARCH_CONDITION_VC           :  nn2_col is not null
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 03-MAR-18
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 0
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : SYS_C0061374
CONSTRAINT_TYPE               : C
TABLE_NAME                    : REDEF_TAB
SEARCH_CONDITION              : "NN_COL" IS NOT NULL
SEARCH_CONDITION_VC           : "NN_COL" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 03-MAR-18
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 0
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : REDEF_TAB_PK
CONSTRAINT_TYPE               : P
TABLE_NAME                    : REDEF_TAB
SEARCH_CONDITION              :
SEARCH_CONDITION_VC           :
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 03-MAR-18
INDEX_OWNER                   : MCDONAC
INDEX_NAME                    : REDEF_TAB_PK
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 0

PL/SQL procedure successfully completed.


SQL>
SQL>
SQL>


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.