Creating Tables in SQL
This document provides a comprehensive guide to creating tables in SQL databases. Tables are the fundamental structures that store data in a relational database.
The CREATE TABLE Statement
The primary statement used to create a table is CREATE TABLE. Its basic syntax involves specifying the table name and then defining its columns, including their names, data types, and optionally, constraints.
Basic Syntax
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
column3 datatype constraints,
...
);Example: Creating a Simple `Customers` Table
Let's consider an example where we create a table named Customers to store information about our clients.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
RegistrationDate DATETIME DEFAULT CURRENT_TIMESTAMP
);Explanation of Components:
CREATE TABLE table_name: This initiates the table creation process, specifying the name of the new table.column_name datatype: Defines a column within the table and its data type (e.g.,INTfor integers,VARCHAR(size)for variable-length strings,DATETIMEfor date and time values).- Constraints: These rules enforce data integrity. Common constraints include:
PRIMARY KEY: Uniquely identifies each row in the table.AUTO_INCREMENT: Automatically assigns a unique sequential number to the primary key when a new row is inserted. (Syntax may vary slightly between database systems).NOT NULL: Ensures that a column cannot have aNULLvalue.UNIQUE: Ensures that all values in a column are distinct.DEFAULT value: Assigns a default value if no value is provided during insertion.FOREIGN KEY: Establishes a link to a column in another table, ensuring referential integrity. (Covered in the Relationships section).
Common SQL Data Types
| Data Type | Description |
|---|---|
INT |
Integer numbers. |
DECIMAL(p, s) |
Exact numeric values with fixed precision and scale (e.g., DECIMAL(10, 2) for currency). |
VARCHAR(size) |
Variable-length character strings. size specifies the maximum length. |
CHAR(size) |
Fixed-length character strings. If the string is shorter than size, it's padded with spaces. |
TEXT |
Large text strings. |
DATE |
Stores date values (YYYY-MM-DD). |
DATETIME |
Stores date and time values (YYYY-MM-DD HH:MI:SS). |
BOOLEAN |
Stores TRUE or FALSE values. |
Adding Constraints Later
You can also add constraints to existing tables using the ALTER TABLE statement. This is useful if you initially create a table without all the necessary constraints.
Example: Adding a UNIQUE Constraint to an Existing Column
ALTER TABLE Customers
ADD CONSTRAINT UQ_Email UNIQUE (Email);CREATE TABLE statement when possible, as it makes the table definition more self-contained and easier to read.
Conclusion
Understanding how to create tables is a foundational skill for any database developer or administrator. By carefully defining your table structures, data types, and constraints, you can build robust and reliable database systems.