Understanding SQL Tables: A Comprehensive Guide to Data Types and Constraints

In the realm of data management, databases serve as structured collections of information. They enable the storage, retrieval, and management of data efficiently. At the core of a database lie tables, which act as the building blocks for organizing this data.

Why Tables?

Tables in a database are similar to spreadsheets; they consist of rows and columns. Each table stores data about a specific entity (like customers, products, or transactions). The rows represent individual records, while the columns signify the attributes or properties of those records.

Using tables allows for:

1. Structured Data Storage: Data is stored in a highly organized manner, facilitating easy access and management.

2. Data Relationships: Tables can be linked to one another using keys, establishing relationships that enrich data integrity and retrieval.

3. Efficient Querying: SQL (Structured Query Language) allows for complex queries across multiple tables, enabling advanced data analysis.

   
   

Creating Tables: The Role of Data Types and Constraints

Before creating a table, it is essential to understand the data types and constraints. Data types define the kind of data that can be stored in each column, while constraints enforce rules on the data within those columns, ensuring data integrity and accuracy.

Data Types (PostgreSQL)

Data types are crucial because they determine how much space is allocated for storing the data and how the data can be manipulated. Here’s a breakdown of the primary SQL data types (most common to create basic tables).

Data Type	Description	Example	Value Example
INT	Stores whole numbers (integers).	age INT	25
SMALLINT	Stores smaller integers.	temperature SMALLINT	70
BIGINT	Stores large integers.	distance BIGINT	1000000000
DECIMAL(p, s)	Stores fixed-point numbers. Precision p defines the total digits, while scale s defines digits after the decimal.	salary DECIMAL(10, 2)	50000.00
NUMERIC(p, s)	Same as DECIMAL, stores fixed-point numbers. The difference between DECIMAL and NUMERIC is mainly semantic.	price NUMERIC(8, 2)	99.99
FLOAT	Stores single-precision floating-point numbers (32 bits).	height FLOAT	5.9
REAL	Stores approximate numeric values, similar to FLOAT.	profit REAL	200.75
DOUBLE PRECISION	Stores double-precision floating-point numbers (64 bits).	weight DOUBLE PRECISION	150.5
BOOLEAN	Stores TRUE or FALSE values.	is_deleted BOOLEAN	FALSE
CHAR(size)	Stores fixed-length strings.	country_code CHAR(3)	'USA'
VARCHAR(size)	Stores variable-length strings.	first_name VARCHAR(50)	'John'
TEXT	Stores large text data.	description TEXT	'This is a movie.'
BYTEA	Stores binary data (for files, images, etc.).	file BYTEA	0xFFD8FFE0...
DATE	Stores date values (YYYY-MM-DD).	birth_date DATE	'1990-01-01'
TIME	Stores time values (HH ).	start_time TIME	'14:30:00'
TIMESTAMP	Stores date and time values.	created_at TIMESTAMP	'2024-10-07 15:00:00'
BIT	Stores binary values (0 or 1).	is_verified BIT	1

Constraints

Constraints are rules applied to columns that ensure the integrity and accuracy of data. Here are the common types of constraints used in SQL:

1. Primary Key (PRIMARY KEY):

   • Ensures each record in the table has a unique identifier. It implicitly enforces that it cannot be NULL. If you try to insert a record with NULL in the movie_id, the database will throw an error indicating that NULL values are not allowed for the primary key.

   • Example:

     movie_id INT PRIMARY KEY

     
     

2. Not Null (NOT NULL):

   • Ensures that a column must always have a value and cannot be left empty. If you try to insert a NULL value into the movie_name column, an error will be raised, preventing the insertion since the column is defined as NOT NULL.

   • Example:

     movie_name VARCHAR(100) NOT NULL

     
     

3. Default Value (DEFAULT):

   • Assigns a default value to a column when no value is provided.

   • Example:

     rental_rate DECIMAL(5, 2) DEFAULT 4.99

     
     

4. Check Constraint (CHECK):

   • Validates that the values in a column meet specific conditions. If you attempt to insert a value that violates a CHECK constraint in a database, an error will be thrown, preventing the insertion of the invalid data.

   • Example:

     CHECK (release_year >= 1888) -- Movies were first released in 1888

     
     

5. Unique Constraint (UNIQUE):

   • Ensures that all values in a column are different from one another, preventing duplicates. It means that duplicate records with the same combination of movie_name and release_year cannot exist in the table; each combination must be unique.

   • Example:

     UNIQUE (movie_name, release_year)

     
     

6. Foreign Key (FOREIGN KEY):

   • Establishes a relationship between two tables. It ensures that the values in one table correspond to values in another table. It ensures data integrity by enforcing that any language_id entered in the Movies table must already exist in the Languages table, allowing for organized and meaningful relationships between the two datasets (e.g., ensuring that each movie is linked to a valid language).

   • Example:

     FOREIGN KEY (language_id) REFERENCES Languages(language_id)

     
     

Example of Creating a Table with Data Types and Constraints

Here’s how you can create a complete SQL table with various data types and constraints:

CREATE TABLE Movies (
    movie_id INT PRIMARY KEY,
    movie_name VARCHAR(100) NOT NULL,
    language_id INT,
    genre VARCHAR(20) NOT NULL,
    rental_rate DECIMAL(5, 2) DEFAULT 4.99,
    release_year INT CHECK (release_year >= 1888),
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (movie_name, release_year)
);

Understanding data types and constraints is fundamental for effective database design. Properly defining data types ensures that your database can efficiently store and retrieve data while constraints maintain data integrity. As you progress in your SQL journey, mastering these concepts will greatly enhance your ability to manage data effectively.