# Database: Tables: Table Commands

These descriptions are close to what is commonly implemented. Each DBMS has its own limitations and extensions.

CREATE TABLE table-name (col-def,... [, constraint-def,...] )

  col-def | ::= column-name data-type [DEFAULT value] [col-constraint...]
  col-constraint | ::= [constraint-name] { NOT NULL | PRIMARY KEY | REFERENCES table-name [(col-name,...)] [delupd-action] | UNIQUE
  }
  constraint-def | ::= PRIMARY KEY (col-name,...) | FOREIGN KEY (col-name,...) REFERENCES table-name [(col-name,...)] [delupd-action] | CHECK (search-condition) | UNIQUE (col-name,...)
  delupd-action | ::= [ON UPDATE ref-action] [ON DELETE ref-action]
  ref-action | ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION -- also called RESTRICT
  constraint-name | ::= CONSTRAINT name

ALTER TABLE table-name alteration

  alteration | ::= ADD [COLUMN] col-def | DROP [COLUMN] col-name [CASCADE | RESTRICT] | ALTER [COLUMN] col-name {SET DEFAULT value | DROP DEFAULT} | ADD {CONSTRAINT name | constraint-def}
  | DROP CONSTRAINT name {CASCADE | RESTRICT}

  Note: An added column can not be NOT NULL without a SET DEFAULT value.
DROP TABLE table-name [CASCADE | RESTRICT]
  Deletes the table. If CASCADE is specified, all objects referring to the table (eg, views) are also dropped. RESTRICT doesn't allow the drop if there are other referring objects.

Notation: UPPERCASE words are SQL keywords. [square brackets] enclose optional elements. ",..." follows an item that can be repeated in a comma-separated list. {curly braces} group elements for use with ",..." or |. | (vertical bar) separates alternatives. Italic words stand for something the user supplies. "::=" means "is defined as".