Lesson 13	Using SQL to Create Tables
Objective	Explain how to use SQL to create Database Tables

Use SQL to Create Database Tables

Once you have created a schema (or database) to contain your objects, the next step is to define the tables that will store your data. The SQL CREATE TABLE statement is the primary mechanism for building the physical structures of a relational database. This lesson explains how to construct tables using modern SQL techniques and best practices found in today’s RDBMS systems such as Oracle Database 23ai, PostgreSQL, SQL Server 2022, and MySQL 8.x.


CREATE TABLE TableName
( Column01Name Column01DataType Column01Constraints,
  Column02Name Column02DataType Column02Constraints,
  ...
  PRIMARY KEY (ColumnXX),
  FOREIGN KEY (ColumnYY) REFERENCES OtherTable(OtherColumn)
);

Although indentation is not required, formatting your SQL statements makes them easier to read and maintain. Each column definition includes three elements:

Column name – the logical field name.
Data type – such as VARCHAR, DATE, INTEGER, or DECIMAL.
Constraints – rules such as NOT NULL, DEFAULT, CHECK, UNIQUE, or foreign keys.

Modern SQL encourages explicit constraints to ensure data quality and reduce application-level validation. The next lesson explains how SQL implements the rest of the physical model.

CREATE TABLE Command in SQL

The CREATE TABLE command defines a new relation by specifying:

A table name
A list of attributes
Data types and constraints
Primary and foreign keys

Attributes must be listed in the order you want them stored. Although row order is not guaranteed in SQL, column order is significant because it defines the physical structure of the table.

Modern DBMS systems often support schema-qualified names. For example:


CREATE TABLE COMPANY.EMPLOYEE (
  ...
);

This explicitly places the table in the COMPANY schema. Without qualification:


CREATE TABLE EMPLOYEE (
  ...
);

the table is created in the active schema selected by the user.

Explicitly Adding EMPLOYEE to the COMPANY Schema

Base tables created with CREATE TABLE are physical relations stored on disk. These are distinct from virtual tables (views), which may not correspond to a physical file.

SQL enforces entity integrity (primary keys), referential integrity (foreign keys), and domain constraints (data types and limits). However, care must be taken when declaring constraints that reference tables not yet created. For example:

EMPLOYEE.Super_ssn references EMPLOYEE.Ssn, a self-referential foreign key.
EMPLOYEE.Dno references DEPARTMENT.Dnumber, which may not yet exist.

To avoid circular references or missing dependencies, you can delay some foreign key declarations and add them afterward via ALTER TABLE.


CREATE TABLE EMPLOYEE
( Fname         VARCHAR(15)       NOT NULL,
  Minit         CHAR(1),
  Lname         VARCHAR(15)       NOT NULL,
  Ssn           CHAR(9)           NOT NULL,
  Bdate         DATE,
  Address       VARCHAR(30),
  Sex           CHAR(1),
  Salary        DECIMAL(10,2),
  Super_ssn     CHAR(9),
  Dno           INT               NOT NULL,
  PRIMARY KEY (Ssn),
  FOREIGN KEY (Super_ssn)
      REFERENCES EMPLOYEE(Ssn),
  FOREIGN KEY (Dno)
      REFERENCES DEPARTMENT(Dnumber)
);

CREATE TABLE DEPARTMENT
( Dname         VARCHAR(15)       NOT NULL,
  Dnumber       INT               NOT NULL,
  Mgr_ssn       CHAR(9)           NOT NULL,
  Mgr_start_date DATE,
  PRIMARY KEY (Dnumber),
  UNIQUE (Dname),
  FOREIGN KEY (Mgr_ssn)
      REFERENCES EMPLOYEE(Ssn)
);

Figure 6.13: SQL Syntax to Create Database Tables

These examples demonstrate fundamental SQL DDL (Data Definition Language) concepts that apply across modern enterprise DBMS platforms.