Create Example

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.

  • The path_name definition requires that the field must be known (NOT NULL) and unique (within this column of the table).

  • The file_size definition requires that the field must be a known integer, with a value that is greater than or equal to zero.

  • The by_id definition requires that the field references (i.e., contains) a valid PRIMARY KEY (file_id) in the file table.

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 NOT NULL.

-- Main.RichMorin - 16 Jun 2003
Topic revision: r3 - 08 Jun 2003, WikiGuest
This site is powered by Foswiki Copyright © by the contributing authors. All material on this wiki is the property of the contributing authors.
Foswiki version v2.1.6, Release Foswiki-2.1.6, Plugin API version 2.4
Ideas, requests, problems regarding CFCL Wiki? Send us email