Java Reference
In-Depth Information
In addition to selecting data type and length, there are various integrity constraints you may need to
apply to the data stored in a column. Integrity constraints are important to ensure consistency and
accuracy.
NULL or NOT NULL
In addition to assigning a data type to a field, SQL lets you specify whether a field is required to contain
valid data or whether it can be left empty. In our example, you may decide that you require first name
and last name, but you may not be particularly concerned about middle initials. In this case, set the
constraints for first name and last name to
NOT NULL
and the constraint for middle initial to
NULL
.
Note
Most database systems default to NULL.
UNIQUE
The
UNIQUE
constraint specifies that no two records can have the same value in a particular column.
They must each be unique. An employee id, for example, should be unique.
PRIMARY KEY
The primary key is used by the database management systems as a unique identifier for a row.
Probably the best choice for a primary-key field is an integer, because integers are much faster to
process than, for example, long strings when processing the table. This is one reason why Oracle
provides a ROWID field that is incremented for each row that is added, and MSAccess offers an
AutoNumber option, making the field always a unique key by default.
Note
NULL
,
UNIQUE
, and
PRIMARY KEY
are the constraints most commonly used, but various
database management systems offer custom constraints, such as Oracle's CHECK,
which lets you define syntactic and logical checks to be performed on field values prior to
insertion.
This brief review of data types, constraints and keys should have given you enough background to
start creating a table. The use of SQL to create tables is covered in the
next section
.
Creating a Table
Now that you know enough about the data you intend to store in your table, you are ready to give your
table a name and write the SQL command to create it. Tables are created using the
CREATE TABLE
statement with a table name, followed in parentheses (()) by a series of column definitions. Here's an
example:
CREATE TABLE tableName ( columnName dataType [constraints],...);
Column definitions simply list the column or field name, followed by the data type and the optional
constraints. Column definitions are separated by commas, as shown here:
CREATE TABLE CONTACT_INFO
(CONTACT_ID INTEGER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
