The CREATE TABLE Statement

• Administrative procedures are vendor-specfic.
• Hence, these scripts are not portable.
• Example: the CREATE TABLE statement creates a new table in a database.

  CREATE TABLE author (
      author_id int primary key not null auto_increment,
      author_name char(50),
      author_address char(100),
      author_dob date,
      author_ssn int )
  

• Basic syntax:

  CREATE TABLE table_name (create_definition)

• The create_definition is a comma-separated list of column_name column_type.
• Common types:

  Type	Description
  char(width)	Fixed-width string
  int[(bytes)]	Integer, defaults to 4 bytes
  float[(bytes)] double[(bytes)]	Floating-point number, defaults to 8 bytes
  date	A date string (format is vendor-specific)
  time	A time string (format is vendor-specific)
  blob	A binary string (maximum length is vendor-specific)

• Optional additional directives further define the column.
• Examples from MySQL:

  Type	Description
  [NOT] NULL	Whether the column can be NULL or not
  PRIMARY KEY	Whether the column is the primary key for this table
  DEFAULT default_value	The default value for the column, if not specified in an INSERT statement
  AUTO_INCREMENT	Whether a new unique value should be generated upon each INSERT statement

Examples from the Title Table for MySQL

• Create the title_id column as an integer that is non-NULL, auto-incrementing, and the primary key for this table

  title_id int not null auto_increment primary key

• Create the name field as an 80-character maximum string

  name char(80) not null

Review

Is the CREATE TABLE statement the same in every database implementation?