Database CREATE TABLE Revised 2 April 2004 Fred

Database - CREATE TABLE Revised: 2 April 2004 Fred Swartz

DB - Creating a Database • Create database with command like – CREATE DATABASE • Common, but not official standard. – CREATE SCHEMA • Standard, but not common. • Create the tables in the database. – CREATE TABLE command. Table name. – For each column • Column name • Datatype (Eg, CHAR(10), INT, . . . ) • Constraints (Eg, NULL, PRIMARY KEY, . . . )

CREATE TABLE syntax • CREATE TABLE table_name ( column_name datatype [NULL | NOT NULL] [, column_name datatype [NULL | NOT NULL]]. . . ) • Syntax notation – – – Uppercase - SQL keywords Lowercase - replace with appropriate values. [ ] - Square brackets enclose optional text. . - Repeat previous element zero or more times. | (vertical bar) - Separates optional elements. More on page 53.

DB - Example CREATE TABLE • Example • create table authors ( au_id char(11) au_lname varchar(40) au_fname varchar(20) phone char(12) address varchar(40) city varchar(20) state char(2) zip char(5) ); not null, null, null

DB - Datatypes • Characters • Numbers – Binary integers. – Decimal integers. – Floating-point numbers. • • Money (may use numbers) Date / Time Binary (eg, images, sound) Automatically generated serial numbers.

DB - Datatypes - Characters • Fixed length – CHAR(n) or CHARACTER(n) – Shorter values padded with blanks. – Longer values are truncated. – Eg, phone char(12) • Variable length – VARCHAR(n) or VARYING CHARACTER(n) – Stores characters with no padding. – May minimize space? Needs research. – Eg, address varchar(40)

DB - Constraints - NULL, NOT NULL • Specifies whether to allow NULL in a column (NULL) or forbid it (NOT NULL). • If no data is entered for a column. – Default is typically NULL. – Default column value can be specified. • Primary keys must not be NULL – Nor have NULL in any part. • Foreign key may be NULL. • Allowing NULLs depends on business rules.