Constraints are rules for a table and its columns that constrain how and what data can be inserted, updated, or deleted.
Constraints are available for both columns and tables.
Columns may have rules that define what list of values may be entered into them.
The NOT NULL constraint means that a column must have a value.
It can't be unknown, or blank.
The following table uses DDL for Creating the Authors Table with NOT NULL Column Constraints.
CREATE TABLE authors (
id number(38) not null,
name varchar2(100) not null,
birth_date date,
gender varchar2(30)
);
Tables may have rules that enforce uniqueness of column values and the validity of relationships to other rows in other tables.
The following discuss only three here: unique key, primary key, and foreign key.
A unique key constraint is a rule on one or more columns that requires their combination of values to be unique.
Columns in a unique index or unique constraint may be NULL.
The following code shows the DDL for Creating a Unique Constraint Against the Author Table.
ALTER TABLE authors
ADD CONSTRAINT authors_uk1
UNIQUE ( name, birth_date, gender );
A primary key constraint is a rule on one or more columns that requires their combination of values to be unique.
You should have a primary key constraint defined for every table in your database.
The following code shows DDL to Create a Primary Key Constraint Against the Authors Table.
ALTER TABLE authors ADD
CONSTRAINT authors_pk
primary key ( id );
The syntax for creating a primary key constraint is as follows:
ALTER TABLE <table_name> ADD
CONSTRAINT <constraint_name>
PRIMARY KEY (
<column_name_1>,
<column_name_2>,...
<column_name_N> );
where <table_name> is the name of the table, <constraint_name> is the name of the primary key constraint, and <column_name> is a column to use in the constraint.
A foreign key is one or more columns from another table that point to, or are connected to, the primary key of the first table.
Foreign keys are defined with foreign key constraints.
A foreign key constraint is defined against a dependent, or child, table.
The following code shows the DDL for Creating a Foreign Key Constraint Against the Author Publications Table.
ALTER TABLE author_books ADD
CONSTRAINT author_books_fk1
FOREIGN KEY (author_id)
REFERENCES authors (id);
The syntax for creating a foreign key constraint is as follows:
ALTER TABLE <table_name> ADD
CONSTRAINT <constraint_name>
FOREIGN KEY (
<column_name_1>,
<column_name_2>,...
<column_name_N> )
REFERENCES <referenced_table_name> (
<column_name_1>,
<column_name_2>,...
<column_name_N> );
<table_name> is the name of the table to be constrained, <constraint_name> is the name of the foreign key constraint, <referenced_table_name> is the name of the table to be referenced, <column_name> is a column that is both part of the referenced table's key and corresponds to a column with the same value in the dependent table.
The following code shows the DDL for Creating a Primary Key Constraint Against the Author Publications Table.
ALTER TABLE author_books ADD
CONSTRAINT author_books_pk
PRIMARY KEY ( id);