Re: Table design and not null columns

Is completely equivalent except for the constraint name. In some cases your solution is to be preferred, because it will allow you to 'parse' the error message, when running pl/sql, and return a meaningfull error message to the end-user.

Hth,

Sybrand Bakker, Oracle DBA

"Teresa Huntsman" <thuntsma_at_uiuc.edu> wrote in message news:3ABFB000.CBD3CD5F_at_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 Tue Mar 27 2001 - 16:47:54 CST