Data Types and Column Constraints

Difficulty: Basic
Generated on: 2025-07-10 02:22:30
Category: SQL Cheatsheet for Database Development

---

SQL Cheatsheet: Data Types and Column Constraints (Basic Level)

This cheatsheet provides a quick reference for SQL data types and column constraints, covering common syntax, use cases, best practices, and potential pitfalls. It aims to be a practical guide for database developers working with various SQL dialects.

1. Quick Overview

• Data Types: Define the kind of data a column can store (e.g., numbers, text, dates). Choosing the right data type is crucial for data integrity, storage efficiency, and performance.
• Column Constraints: Rules enforced on data within a column, ensuring data quality and consistency. Constraints prevent invalid or unwanted data from being inserted into the table.

2. Syntax

2.1 Data Types

Data Type Category	Data Type	Description	Example Values
Numeric	INT / INTEGER	Whole numbers (integers)	-10, 0, 12345
	BIGINT	Large whole numbers	123456789012345
	SMALLINT	Smaller whole numbers	-32768, 32767
	TINYINT	Very small whole numbers	0, 255
	DECIMAL(p, s)	Exact numeric value with p digits, s after the decimal point	123.45, -0.01, 100
	NUMERIC(p, s)	Synonym for DECIMAL	123.45, -0.01, 100
	FLOAT(p)	Approximate numeric value with precision p (single-precision floating-point)	3.14159, -2.718
	REAL	Approximate numeric value (single-precision floating-point)	3.14159, -2.718
	DOUBLE PRECISION	Approximate numeric value (double-precision floating-point)	3.14159265359
String	VARCHAR(n)	Variable-length string, maximum length n	"Hello", "SQL"
	CHAR(n)	Fixed-length string, length n	"ABC", " "
	TEXT	Variable-length string, no maximum length (implementation-dependent)	"Long text here..."
Date/Time	DATE	Date only	'2023-10-27'
	TIME	Time only	'14:30:00'
	DATETIME / TIMESTAMP	Date and time	'2023-10-27 14:30:00'
Boolean	BOOLEAN	True or False value	TRUE, FALSE
Binary	BLOB / BINARY	Binary Large Object (for storing images, videos, etc.)	(Binary data)

Note: Specific data type names and behavior might vary slightly across different database systems (MySQL, PostgreSQL, SQL Server, Oracle).

2.2 Column Constraints

Constraint	Description	Syntax
NOT NULL	Ensures a column cannot contain a NULL value.	column_name data_type NOT NULL
UNIQUE	Ensures all values in a column (or a group of columns) are distinct.	column_name data_type UNIQUE or CONSTRAINT constraint_name UNIQUE (column_name1, column_name2)
PRIMARY KEY	Uniquely identifies each row in a table. Implies NOT NULL and UNIQUE.	column_name data_type PRIMARY KEY or CONSTRAINT constraint_name PRIMARY KEY (column_name)
FOREIGN KEY	Establishes a link between two tables.	column_name data_type REFERENCES other_table(other_column)
CHECK	Specifies a condition that must be true for a value in a column.	column_name data_type CHECK (condition)
DEFAULT	Provides a default value for a column if no value is specified.	column_name data_type DEFAULT default_value

3. Common Use Cases

• INT: Storing IDs, quantities, counters.
• VARCHAR: Storing names, addresses, descriptions.
• DATE: Storing birth dates, order dates.
• BOOLEAN: Storing flags (e.g., is_active, is_admin).
• NOT NULL: Ensuring required fields are always populated (e.g., email, product_name).
• UNIQUE: Preventing duplicate entries (e.g., username, email).
• PRIMARY KEY: Creating a unique identifier for each record.
• FOREIGN KEY: Enforcing relationships between tables (e.g., customer_id in an orders table referencing the customers table).
• CHECK: Validating data (e.g., ensuring age is within a reasonable range, product price is positive).
• DEFAULT: Setting a default value for a new record (e.g., registration_date to the current date).

4. Best Practices

• Choose the smallest appropriate data type: Use SMALLINT instead of INT if the values will always be small. This saves storage space and can improve performance.
• Use VARCHAR instead of TEXT when you know the maximum length: This can improve performance in some databases.
• Use DATE or TIMESTAMP instead of storing dates as strings: This allows for proper date arithmetic and sorting.
• Always define a PRIMARY KEY: This is essential for identifying and accessing records efficiently.
• Use FOREIGN KEY constraints to enforce referential integrity: This prevents orphaned records and ensures data consistency.
• Add indexes to columns used in WHERE clauses or JOIN conditions: This can significantly improve query performance.
• Carefully consider the impact of constraints on performance: Excessive constraints can slow down data insertion and updates. Balance data integrity with performance.
• Use meaningful constraint names: This makes it easier to understand and troubleshoot constraint violations.
• Avoid storing sensitive data in plain text: Consider encryption or hashing.
• Test constraints thoroughly: Ensure that constraints are working as expected and prevent invalid data from being inserted.

5. Examples

-- Example table creation with data types and constraints
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL UNIQUE
);

-- Inserting sample data
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering');

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id) VALUES
(101, 'John', 'Doe', 'john.doe@example.com', '2022-01-15', 60000.00, 1),
(102, 'Jane', 'Smith', 'jane.smith@example.com', '2022-03-01', 75000.00, 2),
(103, 'Peter', 'Jones', 'peter.jones@example.com', '2022-05-10', 90000.00, 3);


-- Example using DEFAULT constraint
-- If is_active is not specified, it defaults to TRUE
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (104, 'Alice', 'Brown', 'alice.brown@example.com', '2023-01-01', 80000, 1);

-- Example using CHECK constraint
-- Attempting to insert a negative salary will result in an error
-- INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
-- VALUES (105, 'Bob', 'Johnson', 'bob.johnson@example.com', '2023-02-15', -50000, 2); -- This will fail

-- Example Query
SELECT * FROM employees WHERE salary > 70000 AND is_active = TRUE;

-- Sample Output for the Select Query (based on the inserted data)
-- employee_id | first_name | last_name |         email         | hire_date  | salary  | department_id | is_active
-- -------------+------------+-----------+-----------------------+------------+---------+---------------+-----------
--         102 | Jane       | Smith     | jane.smith@example.com | 2022-03-01 | 75000.00 |             2 | t
--         103 | Peter      | Jones     | peter.jones@example.com | 2022-05-10 | 90000.00 |             3 | t
--         104 | Alice      | Brown     | alice.brown@example.com | 2023-01-01 | 80000.00 |             1 | t

6. Common Pitfalls

• Incorrect data type: Using the wrong data type can lead to data loss, errors, and performance issues. For example, storing phone numbers as INT will lose leading zeros.
• Forgetting NOT NULL constraints: This can lead to unexpected NULL values in required fields.
• Not defining a PRIMARY KEY: This can make it difficult to uniquely identify and access records.
• Violating UNIQUE constraints: Attempting to insert duplicate values into a column with a UNIQUE constraint will result in an error.
• Incorrectly defining FOREIGN KEY relationships: This can lead to orphaned records and data inconsistencies. Make sure the data type of the foreign key column matches the data type of the primary key column it references.
• Overusing constraints: Excessive constraints can slow down data insertion and updates.
• Ignoring database-specific data type differences: Data type names and behavior can vary across different database systems.
• Not handling constraint violations gracefully: Applications should handle constraint violations gracefully and provide informative error messages to the user.
• Assuming string comparisons are always case-sensitive: String comparison behavior can vary depending on the database and collation settings.

7. Database Variations

Feature	MySQL	PostgreSQL	SQL Server	Oracle
TEXT equivalent	TEXT, MEDIUMTEXT, LONGTEXT	TEXT	VARCHAR(MAX), TEXT	CLOB
BOOLEAN	TINYINT(1) (or BOOL, BOOLEAN)	BOOLEAN	BIT	NUMBER(1) (or VARCHAR2(5) with CHECK)
DATETIME	DATETIME	TIMESTAMP WITHOUT TIME ZONE	DATETIME, DATETIME2	DATE, TIMESTAMP
AUTO_INCREMENT	AUTO_INCREMENT (on PRIMARY KEY)	SERIAL (or GENERATED ALWAYS AS IDENTITY)	IDENTITY(seed, increment)	SEQUENCE and TRIGGER

Notes:

• MySQL: BOOLEAN is internally stored as TINYINT(1). Use AUTO_INCREMENT to automatically generate unique IDs.
• PostgreSQL: SERIAL is a shorthand for creating a sequence and setting the default value for a column. GENERATED ALWAYS AS IDENTITY is the SQL standard alternative.
• SQL Server: VARCHAR(MAX) is used for storing large strings. IDENTITY property is used for auto-incrementing columns.
• Oracle: DATE stores both date and time. TIMESTAMP provides higher precision. Auto-incrementing requires a SEQUENCE and a TRIGGER. NUMBER(1) with a CHECK constraint is a common way to emulate a boolean.
• Always consult the specific database documentation for the most accurate and up-to-date information.