Java Reference
In-Depth Information
CREATE TABLE tableName
( columnName dataType[(size)] [constraints] [default value],...);
Integrity constraints and triggers
It is obvious from the earlier discussion of primary and foreign keys that the idea of
linking tables through the use of keys can go completely haywire if a primary key has
either a NULL value or a value that is not unique. Problems like this are handled using
constraints. The main types of constraint are as follows:
NULL or NOT NULL constraint specifies whether a field is required to contain valid data or
whether it can be left empty.
The UNIQUE constraint specifies that no two records can have the same value in a particular
column.
The PRIMARY KEY constraint specifies that this column is the primary key for the table.
In addition to defining constraints, the SQL language allows the user to specify
security rules that are applied when specified operations are performed on a table.
These rules are known as
triggers
, and work like stored procedures, with the
exception that, instead of being called by name, they are triggered automatically by
the occurrence of a database event such as updating a table.
A typical use of a trigger might be to check the validity of an update to an inventory
table. The following code snippet shows a trigger that automatically rolls back or
voids an attempt to increase the cost of an item in inventory by more than 15 percent:
CREATE TRIGGER FifteenPctRule ON INVENTORY FOR INSERT, UPDATE AS
DECLARE @NewCost money
DECLARE @OldCost money
SELECT @NewCost = cost FROM Inserted
SELECT @OldCost = cost FROM Deleted
IF @NewCost > (@OldCost * 1.15)
ROLLBACK Transaction
Transaction management and the SQL ROLLBACK command are discussed later in
this chapter and in more detail in subsequent chapters.
Data Manipulation Language
The Data Manipulation Language comprises the SQL commands used to insert data
into a table and to update or delete data. SQL provides the following three statements
you can use to manipulate data within a database:
INSERT
UPDATE
