 |
|
Oracle Tips by Burleson |
NOT NULL
The NOT NULL constraint ensures that a column does
not have NULL values. Any value that is inserted or updated in that
column is check to insure that it is not a NULL value. The NOT Null
constraint can be named or be part of the table definition (not have a
name).
create table
editor
(
editor_key varchar2(9) not null,
editor_last_name varchar2(40),
editor_first_name varchar2(30) not null,
editor_hire_date date,
editor_active char(1)
constraint active_ck check (editor_active in ('Y','N')),
constraint ed_name_un unique
(editor_first_name,editor_last_name),
constraint editor_pk primary key (editor_key)
using index tablespace users
);
NOT NULL constraints are automatically defined on
columns with primary key
and unique constraints. If the
table already exists, you can use the ALTER TABLE command to modify
the column.
SQL> alter
table editor modify editor_first_name not null;
Table
altered.
SQL> desc
editor
Name Null? Type
----------------------------------------- -------- --------------
EDITOR_KEY NOT NULL VARCHAR2(9)
EDITOR_LAST_NAME VARCHAR2(40)
EDITOR_FIRST_NAME NOT NULL VARCHAR2(30)
EDITOR_HIRE_DATE DATE
EDITOR_ACTIVE CHAR(1)
SQL> alter
table editor modify editor_first_name null;
Table
altered.
SQL> desc
editor
Name Null? Type
----------------------------------------- -------- -------------- EDITOR_KEY
NOT NULL VARCHAR2(9)
EDITOR_LAST_NAME VARCHAR2(40)
EDITOR_FIRST_NAME VARCHAR2(30)
EDITOR_HIRE_DATE DATE
EDITOR_ACTIVE CHAR(1)
Executing the second example does not change the
values in the column; it allows the column to accept NULLs. By
default, columns will accept NULLs.
The final constraint is used to insure that values
inserted/updated meet a specific condition. It is called a check
constraint
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by
John Garmany
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
