Introduction

Depending on the DBMS, SQL statements can be entered by hand or created using a menu-driven front end that constructs and executes the SQL statements for the user.

The front end is quicker than constructing statements by hand for the novice user, and helps avoid errors.
A good user front end allows complete functionality. A poor one makes it necessary to edit the constructed statements.

CREATE TABLE

The following is a simplified version of the CREATE TABLE code. See CREATE TABLE Statement for the complete syntax.

CREATE TABLE Syntax

The SQL CREATE TABLE statement creates a new table in the database.

        
CREATE TABLE table_name
    (create_definition,...)
    [table_options]

Parameters

create_definition:

              
{
    col_name column_definition
  | {INDEX | KEY} [index_name]
  | [CONSTRAINT [symbol]] PRIMARY KEY
      (key_part,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name]
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
  | check_constraint_definition
}

column_definition:

              
{
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [check_constraint_definition]
  | data_type
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [check_constraint_definition]
}

check_constraint_definition

              
[CONSTRAINT [symbol]] CHECK (expr)

table_options

              
{
  | AUTO_INCREMENT [=] value
  | COMMENT [=] 'string'
}

Examples

Tables can be created with SQL code, or by applications that generate and apply the proper SQL code. The following are examples of both approaches.

The member Table Creation Code

        
CREATE TABLE `member` (
	`memberId` SMALLINT(5) NOT NULL AUTO_INCREMENT,
	`memberSurname` VARCHAR(25) NOT NULL COMMENT 'Surname.'
	`memberForename` VARCHAR(25) COMMENT 'Forename.'
	`memberTitle` VARCHAR(25) COMMENT 'Honorific (Mr, Ms, Dr, etc.)'
	`memberInstitution` LONGTEXT COMMENT 'Associated institution.'
	`memberAddress` LONGTEXT COMMENT 'Address.'
	`memberTelephone` VARCHAR(25) COMMENT 'Telephone number.'
	`memberFax` VARCHAR(25) COMMENT 'Fax number.'
	`memberEmail` VARCHAR(50) COMMENT 'Email.'
	PRIMARY KEY (`memberId`),
	INDEX `inFullName` (`memberSurname`, `memberForename`)
)
COMMENT='Contains information on Members of the DCRIS directory.';

AUTO_INCREMENT assigns a number to memberId when a member is added to the table. It adds 1 to the largest previous value of memberId.
NOT NULL requires a field to have a value when entered. Only memberId and memberSurname are required to have values.
A member of a PRIMARY KEY must be defined with NOT NULL
INDEX instructs SQL to define an index for this table based upon a member's surname and forename, in that order. This greatly speeds name-based searches.

The memberBroad Table Creation Code

        
CREATE TABLE `memberBroad` (
	`memberBroadMemberId` SMALLINT(5) NOT NULL COMMENT 'Foreign key to the member table.',
	`memberBroadBroadId` TINYINT(3) NOT NULL COMMENT 'Foreign key to the broad table.',
	PRIMARY KEY (`memberBroadMemberId`, `memberBroadBroadId`),
	INDEX `inMemberBroadBroad` (`memberBroadBroadId`, `memberBroadMemberId`),
	CONSTRAINT `fkMemberBroadBroad` FOREIGN KEY (`memberBroadBroadId`)
      REFERENCES `broad` (`broadId`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `fkMemberBroadMember` FOREIGN KEY (`memberBroadMemberId`)
      REFERENCES `member` (`memberId`) ON UPDATE CASCADE ON DELETE CASCADE
)
COMMENT='Joins broad to member through a lookup table.';

memberBroadMemberId refers to the memberId field of the member table. No new record may be added to the memberBroad table that does not have a matching memberId in the member table.
The CONSTRAINT FOREIGN KEY ... REFERENCES clause tells SQL how to relate this table to the member and broad tables based upon their primary keys and the matching values in this table. This is a constraint on the values this table may contain.
The ON DELETE CASCADE clause tells the DBMS that when a member is deleted, all of that member's broad expertises should be deleted as well. When a particular memberId is deleted from the member table, all tuples in other tables with a matching memberId are deleted. This is part of keeping data consistent within a database.
The ON UPDATE CASCADE clause is similar to the ON DELETE CASCADE clause in that any change to a primary key value is propagated throughout the database to any other table containing that value as a foreign key reference value.
INDEX instructs SQL to define an index for this table based similar to its primary key, but in reverse order. This speeds up searches based upon the broad expertise ID instead of the member ID.

The borrowed Table Creation Code

        
CREATE TABLE `borrowed` (
	`isbn` CHAR(17) NOT NULL COMMENT 'ISBN of book',
	`dateBorrowed` DATE NOT NULL COMMENT 'Date book borrowed',
	`dateReturned` DATE NULL DEFAULT (curdate()) COMMENT 'Date book returned',
	PRIMARY KEY (`isbn`, `dateBorrowed`),
	CONSTRAINT `fkBook` FOREIGN KEY (`isbn`) REFERENCES `book` (`isbn`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `chkDates` CHECK (`dateReturned` >= `dateBorrowed`)
)
COMMENT='Tracks books borrowed.';

The isbn value is a 'natural' primary key in that it is using a real-world value as a key rather than an auto-generated integer.
dateBorrowed is given the current date (using the predefined function curdate) when a record is added.
dateReturned allows NULL values when created, which makes sense in the context that the book has not yet been returned when it is borrowed. A NULL value for dateReturned implies that the book is still out.
A CONSTRAINT on dateReturned is that it must be greater than or equal to dateBorrowed, which is simply common sense. If dateReturned is NULL when CHECKed, the CHECK clause returns an UNKNOWN result, which can be looked for by an application.
The primary key has more than one attribute and is defined after the attribute definitions.
The CHECK constraint is anonymous.
The foreign key reference lacks the ON DELETE CASCADE clause, meaning that an attempt to delete a matching tuple in the pub table will fail so long as a matching pub_ID exists in the Borrowed table.

Tables can be created with appropriate GUI software. The following are examples of creating the student table with HeidiSQL. In CP363 you are welcome to use an appropriate GUI such as HeidiSQL or MySQL Workbench - you are not required to write your table creation code by hand.

student Table Creation Code

            
CREATE TABLE `student` (
  `studentId` CHAR(9) NOT NULL COMMENT 'Student ID' COLLATE 'utf8mb4_0900_ai_ci',
  `surname` VARCHAR(25) NOT NULL DEFAULT '' COMMENT 'Student surname' COLLATE 'utf8mb4_0900_ai_ci',
  `forename` VARCHAR(25) NOT NULL DEFAULT '' COMMENT 'Student forename' COLLATE 'utf8mb4_0900_ai_ci',
  PRIMARY KEY (`studentId`) USING BTREE,
  INDEX `surname_forename` (`surname`, `forename`) USING BTREE
)
COMMENT='Student information'
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

This is the code that HeidiSQL creates in the previous figure. Note that it has extra clauses that you normally don't have to provide, such as COLLATE and ENGINE - these values are normally set to whatever defaults your server is set to, but the can be specified if necessary.

SQL Table Creation

Introduction

CREATE TABLE

Examples