Here is an example of how one might create the tables used
in Query Example
CREATE TABLE file (
file_id INTEGER NOT NULL PRIMARY KEY,
path_name VARCHAR(1024) NOT NULL UNIQUE,
file_size INTEGER NOT NULL CHECK (file_size >= 0),
CREATE TABLE action (
by_id NOT NULL REFERENCES file(file_id),
action_type VARCHAR(64) NOT NULL,
on_id NOT NULL REFERENCES file(file_id),
purpose VARCHAR(64) NOT NULL,
These examples demonstrate SQL's data validation syntax.
path_name definition requires that the field must be known (
NOT NULL) and unique (within this column of the table).
file_size definition requires that the field must be a known integer, with a value that is greater than or equal to zero.
by_id definition requires that the field references (i.e., contains) a valid
PRIMARY KEY (
file_id) in the
Although data validation is generally a Good Thing,
it must be used with moderation.
If a field is normally known,
but may occasionally be unknown,
one cannot require it to be
-- Main.RichMorin - 16 Jun 2003