SQL Fundamentals: Keys, Constraints, and Basic Concepts

Before we dive into the various SQL language categories and CRUD operations, it is essential to understand some fundamental concepts that form the backbone of relational database design. In this reading assignment, we will explore key database concepts including primary keys, foreign keys, and constraints in PostgreSQL.

Tables

In a relational database, data is organized into tables (also called relations). Each table consists of rows (records) and columns (fields). Think of a table as similar to a spreadsheet, where each column represents a specific attribute or piece of data, each row represents a single record or entity, and the intersection of a row and column contains a specific data value.

Here is an example of a simple students table in PostgreSQL:


        CREATE TABLE students (
            student_id SERIAL,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100),
            date_of_birth DATE,
            enrollment_date DATE DEFAULT CURRENT_DATE
        );

In this example, student_id, first_name, last_name, etc. are columns. SERIAL, VARCHAR(50), DATE, etc. are data types. NOT NULL and DEFAULT CURRENT_DATE are constraints that enforce specific rules about the data.

Keys

Keys are essential elements in relational databases that ensure each record can be uniquely identified and that relationships between tables are properly maintained. They play a critical role in enforcing data integrity, preventing duplicate or orphaned records, and defining how data in different tables is connected. Understanding keys is fundamental to designing reliable and efficient databases.

Primary Keys

A primary key is a column or combination of columns that uniquely identifies each row in a table. Primary keys are fundamental to database design as they ensure each record can be uniquely identified, provide a way to reference specific records, and enforce data integrity by preventing duplicate entries.

In PostgreSQL, we can define a primary key when creating a table:


        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100)
        );

Primary Key Characteristics

A primary key must be unique (each value must be different from all other values in the column), not null (the value cannot be empty or null), and immutable (once assigned, the value should not change, which is a best practice though not strictly enforced by the database).

The SERIAL data type in PostgreSQL automatically creates a sequence that generates unique integer values, making it ideal for primary keys. It is equivalent to:


        student_id INTEGER NOT NULL DEFAULT nextval('students_student_id_seq')

You can also create a composite primary key using multiple columns:


        CREATE TABLE course_enrollments (
            student_id INTEGER,
            course_id INTEGER,
            enrollment_date DATE,
            PRIMARY KEY (student_id, course_id)
        );

Foreign Keys

A foreign key is a column or group of columns in one table that references the primary key of another table. Foreign keys create relationships between tables, enforce referential integrity, and prevent actions that would destroy links between tables.

Here is how to create a foreign key in PostgreSQL:


        CREATE TABLE courses (
            course_id SERIAL PRIMARY KEY,
            course_name VARCHAR(100) NOT NULL,
            department VARCHAR(50),
            credits INTEGER
        );

        CREATE TABLE enrollments (
            enrollment_id SERIAL PRIMARY KEY,
            student_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL,
            grade CHAR(2),
            FOREIGN KEY (student_id) REFERENCES students (student_id),
            FOREIGN KEY (course_id) REFERENCES courses (course_id)
        );

In this example, student_id in the enrollments table is a foreign key that references the student_id primary key in the students table. This creates a relationship where each enrollment must be associated with a valid student.

Referential Integrity

Foreign keys maintain referential integrity by ensuring that you cannot add a record to the child table (enrollments) if the referenced key doesn't exist in the parent table (students), and you cannot delete a record from the parent table if it is referenced by records in the child table (unless you specify an action like CASCADE).

Constraints

Constraints are rules applied to table columns to enforce data integrity. They help ensure that only valid, consistent, and reliable data is stored in your database by enforcing specific requirements or restrictions on the values that can be entered.

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value, meaning a value must be provided.


        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100)
        );

In this example, first_name and last_name cannot be NULL, but email can be NULL.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. Unlike primary keys, unique constraints can allow NULL values (unless combined with NOT NULL) and can be applied to multiple columns in a table.


        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100) UNIQUE,
            student_number VARCHAR(10) UNIQUE NOT NULL
        );

PRIMARY KEY vs. UNIQUE Constraint

A primary key is automatically unique and not null. The main differences are: a table can have only one primary key but multiple unique constraints, unique constraints can allow NULL values (unless combined with NOT NULL) while primary keys cannot, and primary keys are typically used as the main identifier for a record while unique constraints enforce uniqueness for other attributes.

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition.


        CREATE TABLE products (
            product_id SERIAL PRIMARY KEY,
            product_name VARCHAR(100) NOT NULL,
            price DECIMAL(10,2) CHECK (price > 0),
            discount_percent DECIMAL(5,2) CHECK (discount_percent >= 0 AND discount_percent <= 100)
        );

In this example, the CHECK constraints ensure that price is always greater than zero and discount_percent is between 0 and 100.

DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during an insert.


        CREATE TABLE orders (
            order_id SERIAL PRIMARY KEY,
            customer_id INTEGER NOT NULL,
            order_date DATE DEFAULT CURRENT_DATE,
            status VARCHAR(20) DEFAULT 'Pending'
        );

In this example, if no value is provided for order_date, it defaults to the current date, and if no value is provided for status, it defaults to 'Pending'.

Relationships Between Tables

Relational databases derive their power from the ability to establish relationships between tables. These relationships are implemented using foreign keys and help model real-world connections between different types of data.

One-to-One (1:1)

In a one-to-one relationship, one record in the first table is related to exactly one record in the second table.


        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL
        );

        CREATE TABLE student_details (
            detail_id SERIAL PRIMARY KEY,
            student_id INTEGER UNIQUE,  -- Ensures one-to-one relationship
            address TEXT,
            phone VARCHAR(20),
            medical_notes TEXT,
            FOREIGN KEY (student_id) REFERENCES students (student_id)
        );

The UNIQUE constraint on student_id in the student_details table ensures that each student can have only one detail record.

One-to-Many (1:N)

In a one-to-many relationship, one record in the first table can be related to multiple records in the second table, but each record in the second table is related to only one record in the first table.


        CREATE TABLE departments (
            department_id SERIAL PRIMARY KEY,
            department_name VARCHAR(100) NOT NULL
        );

        CREATE TABLE employees (
            employee_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            department_id INTEGER,
            FOREIGN KEY (department_id) REFERENCES departments (department_id)
        );

In this example, one department can have many employees, but each employee belongs to only one department.

Many-to-Many (N:M)

In a many-to-many relationship, one record in the first table can be related to multiple records in the second table, and vice versa. This requires a junction table (also called a bridge or linking table).


        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL
        );

        CREATE TABLE courses (
            course_id SERIAL PRIMARY KEY,
            course_name VARCHAR(100) NOT NULL,
            credits INTEGER
        );

        -- Junction table for the many-to-many relationship
        CREATE TABLE enrollments (
            student_id INTEGER,
            course_id INTEGER,
            enrollment_date DATE DEFAULT CURRENT_DATE,
            grade CHAR(2),
            PRIMARY KEY (student_id, course_id),  -- Composite primary key
            FOREIGN KEY (student_id) REFERENCES students (student_id),
            FOREIGN KEY (course_id) REFERENCES courses (course_id)
        );

In this example, one student can enroll in many courses, one course can have many students, and the enrollments junction table resolves this many-to-many relationship by creating two one-to-many relationships.

Basic Data Types in PostgreSQL

PostgreSQL offers a wide variety of data types to store different kinds of information efficiently. Choosing the right data type is important for both performance and data integrity.

Numeric Types

INTEGER or INT - Whole numbers (no decimals)
SERIAL - Auto-incrementing integer, typically used for IDs
DECIMAL(p,s) or NUMERIC(p,s) - Exact numeric with specified precision (p) and scale (s)
REAL, DOUBLE PRECISION - Floating-point numbers

Character Types

CHAR(n) - Fixed-length character string
VARCHAR(n) - Variable-length character string with a limit
TEXT - Variable-length character string without a specified limit

Date and Time Types

DATE - Calendar date (year, month, day)
TIME - Time of day
TIMESTAMP - Date and time
INTERVAL - Time period

Boolean and Other Types

BOOLEAN - Stores true/false values
UUID - Universally unique identifiers
JSON, JSONB - JSON data
ARRAY - Array of a specific type

Check Your Understanding

Practice what you've learned by completing these coding challenges. First, examine a table definition and identify its key components, then create your own table by filling in the missing syntax. Complete each challenge separately and use the copy icon above each code block to get individual feedback from your AI assistant.


        /**
         * Look at this table definition and identify:
         * 1. Which column is the primary key and why?
         * 2. Which columns have constraints and what do they do?
         * 3. What would happen if you tried to insert a student with no first_name?
         */
        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100) UNIQUE,
            enrollment_date DATE DEFAULT CURRENT_DATE
        );


        /**
         * Complete this table definition for a simple library system. Fill in the missing parts:
         * 1. Choose an appropriate primary key and data type
         * 2. Add a NOT NULL constraint where it makes sense
         * 3. Add a UNIQUE constraint where it makes sense
         * 4. Set a default value of 'Available' for the publication status
         */
        CREATE TABLE books (
            _____ _____ _____ _____,          -- Primary key column
            title VARCHAR(200) _____,         -- Should this be required?
            author VARCHAR(100),
            isbn VARCHAR(20) _____,           -- Should this be unique?
            publication_year INTEGER,
            status VARCHAR(20) _____ '_____'  -- Default to 'Available'
        );

Key Concepts Summary

This reading introduced you to fundamental SQL concepts in PostgreSQL that form the foundation of relational database design. You learned that tables organize data into rows and columns, with each table representing a specific entity or concept in your application.

Primary keys serve as unique identifiers for each record and are automatically unique and not null. Foreign keys establish relationships between tables and enforce referential integrity, ensuring that data connections remain valid. Constraints like NOT NULL, UNIQUE, CHECK, and DEFAULT enforce rules for data integrity, preventing invalid or inconsistent data from entering your database.

You also explored the three main types of relationships: one-to-one (where records correspond directly), one-to-many (the most common type, where one record relates to multiple others), and many-to-many (which requires junction tables to resolve the complexity). Finally, you learned about PostgreSQL's rich set of data types, from basic integers and text to more advanced types like JSON and arrays.

Understanding these concepts is essential before diving into SQL language categories (DDL, DML, DQL, etc.) and CRUD operations. In the next reading assignment, we will build on this foundation to explore how to interact with databases through different types of SQL commands.

Explore Further

Now that you understand the fundamental building blocks of relational databases, you might want to explore some of these topics independently:

Research database normalization rules and why they help eliminate data redundancy
Explore CASCADE options for foreign keys and when they might be useful or dangerous
Look into PostgreSQL-specific features like ENUM types, custom data types, and advanced constraints
Investigate indexing strategies and how they improve query performance
Study real-world database schemas from open-source projects to see these concepts in action