Designing Tables: Columns, Data Types, and Constraints

Why Data Types and Constraints Matter

Before diving into the syntax, it's crucial to understand why this is so important. Getting this right from the start prevents countless data quality issues down the line. Let's watch a short video segment that highlights the pitfalls of getting this wrong.

As the video highlighted, choosing the correct data types and constraints is about:

• Data Integrity: Preventing invalid data (like text in a number field).
• Performance: Allowing the database to store, index, and search data efficiently.
• Storage: Using only the necessary amount of disk space.

Part 1: Choosing Appropriate Data Types

Data types specify the kind of data a column can hold. SQL data types can be broadly grouped into three main categories.

Let's explore these categories in more detail using a comprehensive guide.

Here’s a summary of the key takeaways, connecting back to your programming background:

• Numeric Types:

  • INT, SMALLINT, BIGINT: For whole numbers. Choosing the smallest one that fits your expected range of values is most efficient (similar to choosing short vs. int vs. long long in C).
  • DECIMAL(p, s) or NUMERIC(p, s): For numbers where precision is critical, like currency. It stores a number with a fixed precision (p, total digits) and scale (s, digits after decimal). This is for exact arithmetic.
  • FLOAT, REAL: For floating-point numbers where absolute precision is less important (e.g., scientific measurements).

• Character/String Types:

  • VARCHAR(n): The most common string type. It stores a variable-length string up to a maximum of n characters. It only uses storage for the characters you actually enter. This is analogous to a char* pointing to a dynamically allocated string in C.
  • CHAR(n): A fixed-length string. If you define a column as CHAR(10) and store "SQL", it will still use 10 characters of space (padded with spaces). This can be efficient for data that is always the same length, like country codes ('US', 'IN', 'CA'). This is like a char my_string[10]; array in C.
  • TEXT: For very long strings, like product descriptions or article content.

• Date/Time Types:

  • DATE: Stores a date (YYYY-MM-DD).
  • TIME: Stores a time (HH:MI:SS).
  • DATETIME or TIMESTAMP: Stores both date and time. TIMESTAMP is often used for tracking when a row was created or last updated.

Part 2: Enforcing Rules with Constraints

Constraints are rules applied to columns to enforce data integrity. Let's see them in action.

Constraint Summary

Let's consolidate what you've just seen.

1. NOT NULL

• Purpose: Guarantees that a column cannot have a NULL (empty) value.
• Example: An email or username column should always be NOT NULL.
• Syntax: email VARCHAR(100) NOT NULL

2. UNIQUE

• Purpose: Ensures that every value in a column (or a set of columns) is unique across all rows.
• Example: User emails must be unique to prevent duplicate accounts.
• Syntax: email VARCHAR(100) UNIQUE

UNIQUE vs. PRIMARY KEY
You might notice that PRIMARY KEY also enforces uniqueness. So what's the difference?

The key difference is that a table can have only one PRIMARY KEY, which is its main identifier. However, a table can have multiple UNIQUE constraints to enforce other business rules (e.g., user_id is the PRIMARY KEY, but email and username must also be UNIQUE).

3. DEFAULT

• Purpose: Sets a default value for a column if no value is specified during an INSERT.
• Example: Setting an account_status column to 'pending' by default.
• Syntax: account_status VARCHAR(20) DEFAULT 'pending'

4. CHECK

• Purpose: Validates that a column's value meets a specific logical condition.
• Example: Ensuring an age column is greater than 18 or a price is greater than 0.
• Syntax: age INT CHECK (age >= 18)

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_code CHAR(10) UNIQUE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0
);

Conclusion

In this lesson, you've taken a major step in designing robust and reliable databases. By carefully defining column data types and applying constraints, you are enforcing data integrity at the lowest level—the database itself. This makes your data more predictable, your queries more efficient, and your applications more stable.

Key Takeaways:

• Data Types define what kind of data a column can store. Choosing the most appropriate and efficient type is crucial for performance and storage.
• Constraints are rules that enforce data integrity.
  • NOT NULL: Ensures a value must be provided.
  • UNIQUE: Ensures all values in the column are distinct.
  • DEFAULT: Provides a fallback value during insertion.
  • CHECK: Validates data against a custom condition.
• A PRIMARY KEY is a special constraint that is implicitly UNIQUE and NOT NULL, and a table can only have one.

Preview of the Next Lesson:
We have now designed a solid table structure with CREATE TABLE and defined its columns with precise data types and rules. But our tables are still empty. In the next lesson, we will shift from DDL (Data Definition Language) back to DML (Data Manipulation Language) and learn how to populate our tables with data using the INSERT statement.