Create tables

A relational database consists of multiple interlinked tables holding specific data. Defining tables appropriately is crucial for robust data analysis. SQL provides a flexible CREATE TABLE statement for this. This lesson covers its syntax, components like columns and constraints as well as common data types for modeling table schemas effectively.

Create Table Statement

Tables are created using:

CREATE TABLE table_name (
   column1 data_type,
   column2 data_type,
   ...
);

Constraints and defaults can be defined during creation for concise schemas.

The IF NOT EXISTS clause tells SQL to only create the table if it does not already exist. If the table does exist, then no action will be taken and no error will occur.

Here is an example of using the IF NOT EXISTS clause when creating a table in SQL:

CREATE TABLE IF NOT EXISTS customers (
   id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100)
);

Some key points about using IF NOT EXISTS:

• Avoid errors trying to create tables that already exist
• Useful in reusable schema scripts where table may or may not exist
• Ensures the CREATE TABLE statement is idempotent
• Available in MySQL, Postgres, SQL Server, SQLite
• Checks the table name - allows creating new tables with same columns

For example, you may have a database restore script that truncates and recreates tables with test data. Using IF NOT EXISTS handles cases where the tables may or may not be there already without failing.

It also handles situations where multiple developers are running the same table create scripts, avoiding duplication errors.

The check is only on the table name. The same CREATE TABLE IF NOT EXISTS statement can be used to create a table with the same name but different column definitions without issue.

Table Schema Definition

Key components while defining schemas:

Column Names: Descriptive identifiers like user_id, amount_due

Data Types: Constrain stored values like INTEGER, VARCHAR, DATE

Constraints: Restrictions like NOT NULL, UNIQUE and FOREIGN KEYS

Default Values: Pre-populated column values if unspecified

Appropriate schema definition ensures data integrity.

SQL Data Types

Common types include:

Numerical

• INTEGER, BIGINT: Whole numbers
• FLOAT, DOUBLE: Fractional numbers
• BOOLEAN: True/false values

Strings

• CHAR(N): Fixed length string
• VARCHAR(N): Variable length string
• TEXT: Freeform text

Date/Time

• DATE: Calendar dates
• TIME: Time of day
• DATETIME: Date and time

Large Objects

• BLOB: Images, files

Each database defines additional types like monetary, geospatial etc. For more information about these types visit Data types.

Table Constraints

Constraints ensure data validity:

• PRIMARY KEY: Unique row identifier
• FOREIGN KEY: Reference from child to parent table
• NOT NULL: Mandatory value
• UNIQUE: Distinct values
• CHECK: Condition for values

Primary key constraints

A primary key is a special relational database constraint that uniquely identifies each record in a table.

The main characteristics of a primary key are:

• Uniquely identifies each row - No two records can have the same primary key value
• Cannot contain NULL values - Primary key columns must have a defined value
• Prevents duplicate entries - Ensures no duplicate rows exist in a table

A table can only have one primary key. The primary key can consist of a single column or multiple columns (composite key).

Some examples:

Single column primary key

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

Here the id column is the primary key.

Multi-column primary key

CREATE TABLE employee (
  emp_id INT,
  department_id INT,
  PRIMARY KEY (emp_id, department_id)
);

Here the combination of emp_id and department_id forms the primary key.

Primary keys are an important concept in the relational model. They allow uniquely identifying tuples/rows in a table and form the basis for relationships between tables.

Foreign key constraints

A foreign key in a database table is a field that links to the primary key of another table. It helps establish relationships between tables and enforce referential integrity.

The main features of a foreign key:

• It must match the data type and structure of the primary key it references
• The table containing the foreign key is called the child table
• The referenced table with the primary key is called the referenced or parent table
• Helps connect tables based on logical relationships
• Enforces referential integrity - the child table cannot contain orphan records

For example:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Here customer_id in orders table is a foreign key referring to the id primary key in the customers table.

This ensures:

• The data type of customer_id matches id in customers
• Deleting a customer record also deletes associated orders
• Inserting an order requires the customer_id to exist in customers

The foreign key establishes a defined relationship between the two tables promoting discipline and integrity across records. Enforcing foreign key constraints is vital for accurate interconnected data.

Carefully defining constraints prevents invalid data insertion.