Re: Table design and not null columns

From: Teresa Huntsman <thuntsma_at_uiuc.edu>
Date: Mon, 26 Mar 2001 15:09:20 -0600
Message-ID: <3ABFB000.CBD3CD5F@uiuc.edu>

Thanks. One other question: Does the column have to be specified with "not null" in the create statement, or can the "not null" restriction be in a constraint clause? If I do the following:

create table temp_table (

field1 varchar2(10) not null,
field2 varchar2(10),
field3 varchar2(10) not null,

constraint nn_field2 check (field2 is not null) );

then

select * from user_tab_columns
where table_name = 'TEMP_TABLE'
order by column_id;

the "nullable" value for field2 is "Y" ("N" for field1 and field3).

Does it make a difference which method is used? It is nice to have a constraint with an assigned name (nn_field2).

Sybrand Bakker wrote:

> "Teresa Huntsman" <thuntsma_at_uiuc.edu> wrote in message
> news:3ABF97E5.671EAB79_at_uiuc.edu...
> > Sorry if this has been asked before.
> >
> > Is it true that good table design has all the "not null" columns first
> > in the create statement with nullable columns following? Someone told
> > me that they thought this allows for more efficient data storage and
> > data retrieval. I couldn't find any information about this.
> >
>
> This is true. A trailing null allowed column with the value null is not
> stored at all.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
Received on Mon Mar 26 2001 - 15:09:20 CST