Re: Table design and not null columns

Hi Teresa,
My personal thought is that it's best to always name your constraints. Further, with respect to NOT NULL constraints I put them (with a name) using the column constraint syntax as in your example. I suppose it's personal preference as much as anything.

Hope this helps,
Steve

Teresa Huntsman wrote:

> 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 Wed Mar 28 2001 - 08:32:48 CST