Skip to Main Content
  • Questions
  • Constraints - Table and Column Level

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, senthil.

Asked: July 26, 2010 - 6:36 pm UTC

Last updated: August 02, 2010 - 9:08 am UTC

Version: 10.0.0

Viewed 1000+ times

You Asked

Hi,

1. What the difference between table level and column level constraints and how it works?
2. Why cant we add not null constraint at table level and what is the reason?

and Tom said...

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2665518700346300293

A not null constraint only ever affects a single column - you cannot say "x,y,z are NOT NULL", you can only say "x is not null, y is not null, z is not null". Therefore the need to specify this at the table level does not exist. You never ever have to deal with more than a single column at a time.

Rating

  (6 ratings)

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

Comments

Not Null at table level

Michel Cadot, July 28, 2010 - 2:44 pm UTC


However you can specify a NOT NULL constraint at table level like:
SQL> create table t (id integer, val varchar2(10), constraint nn_id check (id is not null));

Table created.

But this is a constraint you find in user_constraints and not the column NOT NULL property you find in user_tab_columns and in DESCRIBE SQL*Plus command like in:
SQL> create table t2 (id integer not null, val varchar2(10));

Table created.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ID                                        NUMBER(38)
 VAL                                       VARCHAR2(10 CHAR)

SQL> desc t2
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ID                               NOT NULL NUMBER(38)
 VAL                                       VARCHAR2(10 CHAR)

SQL> select table_name, column_name, nullable
  2  from user_tab_columns
  3  where table_name in ('T','T2')
  4    and column_name = 'ID'
  5  /
TABLE_NAME                     COLUMN_NAME                    N
------------------------------ ------------------------------ -
T                              ID                             Y
T2                             ID                             N

SQL> select constraint_name, constraint_type, search_condition
  2  from user_constraints 
  3  where table_name = 'T';
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------
NN_ID                          C id is not null

More nothing prevent you from having both constraints for the same column:
SQL> select constraint_name, constraint_type, search_condition
  2  from user_constraints 
  3  where table_name = 'T2';
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------
SYS_C007265                    C "ID" IS NOT NULL
NN_ID2                         C id is not null

The first one is generated by the "NOT NULL" property I gave on CREATE TABLE and the second one by the ALTER TABLE statement.

Regards
Michel
Tom Kyte
July 29, 2010 - 7:55 am UTC

that is not a not null constraint though - it is a check constraint. It is NOT AS GOOD as a not null constraint!!!!


ops$tkyte%ORA11GR2> create table t1 (x int, y char(200), constraint x_not_null check (x is not null) );

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> create table t2 (x int NOT NULL, y char(200) );

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t1_idx on t1(x);

Index created.

ops$tkyte%ORA11GR2> create index t2_idx on t2(x);

Index created.

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100K|    29   (0)| 00:00:01 |
-------------------------------------------------------------------

ops$tkyte%ORA11GR2> select count(*) from t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1058009622

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |  <b> INDEX FULL SCAN| </b>T2_IDX |   100K|     0   (0)| 00:00:01 |
-------------------------------------------------------------------

ops$tkyte%ORA11GR2> select /*+ index( t1 t1_idx ) */ count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100K|    29   (0)| 00:00:01 |
-------------------------------------------------------------------

ops$tkyte%ORA11GR2> set autotrace off



the optimizer will not understand in this case that X is not null, use the NOT NULL constraint.

Maybe one day

Michel Cadot, July 29, 2010 - 8:07 am UTC


Thanks Tom.

I hope than one day the optimizer will be able to recognize this not null constraint in a check one or the create/alter table will be able to set the not null property when it is included in a check constraint (which may not be easy if the text of the constraint text is complex).

Regards
Michel

Interesting

djb, July 29, 2010 - 9:10 pm UTC

I'm curious, the last time I looked at DBA_CONSTRAINTS, it appeared that NOT NULL constraints were converted to a check constraint. I'm guessing there's something I'm missing. What is it?
Tom Kyte
August 02, 2010 - 7:56 am UTC

they are reported that way, they are enforced that way - but they are specially flagged as "not null"


The reporting and enforcing are different than the "fact". There is the fact that is known to the optimizer "this column is NOT NULL", then there is the implementation of enforcement - they are separate.

To djb

Michel Cadot, July 30, 2010 - 2:46 am UTC


As you can see in the example I posted above, the NOT NULL constraint is not converted to a check one but a check one is added to the NOT NULL one.

Regards
Michel

how to make a not-nullable column nullable ?

Sokrates, August 02, 2010 - 4:50 am UTC


very interesting !

11.2 > create table temp(i int not null);

Table created.

11.2 > desc temp
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 I        NOT NULL NUMBER(38)

11.2 > select constraint_name, search_condition from user_constraints where table_name='TEMP';

CONSTRAINT_NAME
--------------------------------------------------------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
SYS_C0048011
"I" IS NOT NULL



question:
what is the difference between


11.2 > alter table temp drop constraint SYS_C0048011;

Table altered.

11.2 > desc temp
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 I          NUMBER(38)



and

11.2 > alter table temp modify(i null);

Table altered.

11.2 > desc temp
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 I          NUMBER(38)



?
Tom Kyte
August 02, 2010 - 9:08 am UTC

the difference is that the latter would be preferred since it semantically says better what you are doing.

However, under the covers they do the same thing.

ok thanks

Sokrates, August 02, 2010 - 9:11 am UTC