The SQL Statement CREATE TABLE

The SQL Statement CREATE TABLE

Basic Syntax

The basic syntax for the CREATE TABLE statement is as follows: 

CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
    [table_constraints]
);

Components of the CREATE TABLE Statement

• table_name: The name of the table you want to create.
• column1, column2, …: The columns in the table, each with a specified data type and optional constraints.
• data_type: Defines the type of data the column will store (e.g., VARCHAR, INT, DATE).
• constraints: Rules applied to the column (e.g., NOT NULL, UNIQUE, DEFAULT).
• table_constraints: Constraints that apply to the table as a whole, such as primary keys and foreign keys.

Column Data Types

Here’s an overview of common data types used in SQL:

• Character Types: CHAR, VARCHAR, TEXT
  • CHAR(n): Fixed-length character string.
  • VARCHAR(n): Variable-length character string with a maximum length of n.
  • TEXT: Variable-length text.
• Numeric Types: INT, FLOAT, DECIMAL
  • INT: Integer.
  • FLOAT: Floating-point number.
  • DECIMAL(p, s): Fixed-point number with precision p and scale s.
• Date and Time Types: DATE, TIME, DATETIME
  • DATE: Date value.
  • TIME: Time value.
  • DATETIME: Date and time value.
• Large Object Types: BLOB, CLOB
  • BLOB: Binary large object.
  • CLOB: Character large object.

Column Constraints

• NOT NULL: Ensures that a column cannot have NULL values.
• UNIQUE: Ensures that all values in a column are unique.
• PRIMARY KEY: Uniquely identifies each row in the table. Automatically implies NOT NULL and UNIQUE.
• FOREIGN KEY: Establishes a relationship between columns in different tables.
• DEFAULT: Provides a default value for the column if none is specified.

Table Constraints

• PRIMARY KEY: A table can have only one primary key, which can consist of one or multiple columns.
• FOREIGN KEY: Defines a relationship between columns in different tables, enforcing referential integrity.
• CHECK: Ensures that all values in a column meet a specific condition.

Examples for Different DBMSs

PostgreSQL 

CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE DEFAULT CURRENT_DATE,
    Salary NUMERIC(10, 2),
    CONSTRAINT chk_Salary CHECK (Salary > 0)
);

• SERIAL: Automatically increments the EmployeeID.
• NUMERIC(10, 2): Stores a number with up to 10 digits, 2 of which are after the decimal point.
• CONSTRAINT: Defines a check constraint on the Salary column.

MySQL 

CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE DEFAULT CURRENT_DATE,
    Salary DECIMAL(10, 2),
    CHECK (Salary > 0)
);

• AUTO_INCREMENT: Automatically increments the EmployeeID.
• DECIMAL(10, 2): Stores a number with up to 10 digits, 2 of which are after the decimal point.

SQL Server 

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATE DEFAULT GETDATE(),
    Salary DECIMAL(10, 2),
    CONSTRAINT chk_Salary CHECK (Salary > 0)
);

• IDENTITY(1,1): Automatically generates incrementing numbers for EmployeeID.
• NVARCHAR(50): Variable-length Unicode string.
• GETDATE(): Provides the current date and time.

Oracle 

CREATE TABLE Employees (
    EmployeeID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    FirstName VARCHAR2(50) NOT NULL,
    LastName VARCHAR2(50) NOT NULL,
    HireDate DATE DEFAULT SYSDATE,
    Salary NUMBER(10, 2),
    CONSTRAINT chk_Salary CHECK (Salary > 0)
);

• NUMBER GENERATED BY DEFAULT AS IDENTITY: Automatically generates incrementing numbers for EmployeeID.
• VARCHAR2(50): Variable-length string in Unicode.

Best Practices

• Choose Descriptive Names: Use meaningful names for tables and columns.
• Define Constraints: Apply appropriate constraints to ensure data integrity.
• Use Defaults Wisely: Set default values where applicable to avoid NULLs and ensure consistency.
• Optimize Data Types: Choose the most appropriate data types to balance storage and performance.

Reviewing Table Structure

After creating a table, you can review its structure using SQL commands specific to your DBMS:

• PostgreSQL:

\d table_name

• MySQL:

DESCRIBE table_name;

• SQL Server:

EXEC sp_help 'table_name';

• Oracle:

SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';

Conclusion

The CREATE TABLE statement is a fundamental part of SQL, allowing you to define and structure your database tables. By understanding its components, options, and variations across different DBMSs, you can effectively design and manage your database schema.