Scribd
Upload
50 views1 page

Chapter 1: Creating: NOT NULL Constraint

Column constraints in databases provide validation rules to ensure only valid data is stored. They help catch programming errors early and prevent invalid data from being inserted by any method. The common types of column constraints are NOT NULL, CHECK, PRIMARY KEY, UNIQUE, REFERENCES, and DEFAULT. Constraints must be satisfied before operations are allowed to proceed or they will be rejected.

Uploaded by

Nishki Gejmer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
50 views1 page

Chapter 1: Creating: NOT NULL Constraint

Column constraints in databases provide validation rules to ensure only valid data is stored. They help catch programming errors early and prevent invalid data from being inserted by any method. The common types of column constraints are NOT NULL, CHECK, PRIMARY KEY, UNIQUE, REFERENCES, and DEFAULT. Constraints must be satisfied before operations are allowed to proceed or they will be rejected.

Uploaded by

Nishki Gejmer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

22 Chapter 1: Creating

silently and automatically repaired. When a constraint succeeds, the operation is


allowed to proceed.
Column constraints are the programmers friend. They provide an early
warning about mistakes involving data. The more constraints there are in a data-
base, the more likely it is that programming errors will be found very early in
the development process, and thats a good thing.
Constraints are also the database administrators friend because they make
it very hard to store invalid data. You can use a column constraint to store a
business-related validation rule in a single central location (the database) and
have it apply to all applications and ad hoc updates. No program or power user
can escape or bypass a column constraint.
<column_constraint> ::= NOT NULL
| [ <constraint_prefix> ]
CHECK "(" <boolean_expression> ")"
| [ <constraint_prefix> ]
PRIMARY KEY [ <clustering> ]
| [ <constraint_prefix> ]
REFERENCES
[ <owner_name> "." ]
<table_name>
[ "(" <column_name> ")" ]
[ <on_action> ]
[ <clustering> ]
| [ <constraint_prefix> ]
UNIQUE [ <clustering> ]
<constraint_prefix> ::= CONSTRAINT [ <constraint_name> ]
<constraint_name> ::= <identifier>
<boolean_expression> ::= see <boolean_expression> in Chapter 3, "Selecting"
<clustering> ::= CLUSTERED
| NONCLUSTERED
<table_name> ::= <identifier>
<on_action> ::= ON UPDATE <action>
| ON UPDATE <action> ON DELETE <action>
| ON DELETE <action>
| ON DELETE <action> ON UPDATE <action>
<action> ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT

1.10.1 NOT NULL Constraint


The NOT NULL constraint prevents NULL values from being stored in the col-
umn. This constraint is recommended for all columns whenever possible, to
simplify expressions, queries, and other SQL statements. For a discussion of
NULL and its relationship to the special three-value logic system using TRUE,
FALSE, and UNKNOWN values, see Section 3.12, Boolean Expressions and
the WHERE Clause.

1.10.2 Column CHECK Constraint


The column CHECK constraint allows a search condition to be tested every
time a column is changed. A search condition can range from a simple compari-
son like CHECK ( salary >= 0 ) to much more complex expressions involving
all of the features available in a query WHERE clause. Search conditions are
described in more detail in Chapter 3, Selecting.

You might also like