Database Constraints in DaMeng

What Are Constraints?

Constraints are rules enforced on table columns to insure data integrity. They guarantee both accuracy and validity of stored data.

Constraint Types

Primary Key Constraint

A primary key uniquely identifies each row in a table. This field must contain unique, non-null values.

Creating a table with a primary key:

CREATE TABLE sales.departments(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO sales.departments VALUES(101, 'Marketing');
-- Success

INSERT INTO sales.departments VALUES(101, 'Sales');
-- Fails: duplicate key

To add a primary key to an existing table:

ALTER TABLE "schema_name"."table_name" ADD PRIMARY KEY("column_name");

NOT NULL Constraint

Columns with this constraint must contain a value—null entries are prohibited.

CREATE TABLE logistics.shipments(
    shipment_id INT PRIMARY KEY,
    destination VARCHAR(100) NOT NULL
);

INSERT INTO logistics.shipments VALUES(5001, 'Shanghai');
-- Success

INSERT INTO logistics.shipments VALUES(5002, NULL);
-- Fails: NULL not allowed

UNIQUE Constraint

This constraint prevents duplicate values within a column, though multiple nulls may be permitted depending on the database configuration.

CREATE TABLE hrm.employees(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    employee_code VARCHAR(20) UNIQUE
);

INSERT INTO hrm.employees VALUES(1, 'Zhang Wei', 'EMP001');
-- Success

INSERT INTO hrm.employees VALUES(2, 'Li Ming', 'EMP001');
-- Fails: duplicate value

Adding a unique constraint via ALTER:

ALTER TABLE "schema_name"."table_name" ADD CONSTRAINT constraint_name UNIQUE("column_name");

Foreign Key Constraint

A foreign key establishes a link between two tables. The table containing the foreign key is the child table, while the referenced table is the parent table.

ALTER TABLE "schema_name"."child_table" ADD CONSTRAINT fk_name FOREIGN KEY ("foreign_key_column") REFERENCES "parent_table" ("primary_key_column");

Check Constraint

Check constraints validate that column values satisfy a specified condition.

ALTER TABLE "schema_name"."table_name" ADD CONSTRAINT constraint_name CHECK("condition_expression");

Data Query Strategies

Subqueries

A subquery is a query nested within another query. The inner query's result serves as a condition, source, or computed value for the outer query.

Subquery Variations

Derived Table Subquery: Treat the inner query result as a temporary table.

SELECT column_list FROM (
    SELECT dept_id, COUNT(*) AS headcount
    FROM hrm.staff
    GROUP BY dept_id
) AS dept_summary WHERE headcount > 10;

WHERE Clause Subquery: Use inner query results as filtering conditions.

SELECT emp_name FROM hrm.employees
WHERE dept_id IN (
    SELECT dept_id FROM hrm.departments WHERE location = 'Beijing'
);

SELECT Clause Subquery: Include computed values from inner queries in the result set.

SELECT emp_name, (
    SELECT COUNT(*) FROM hrm.employees e2
    WHERE e2.dept_id = e1.dept_id
) AS dept_total
FROM hrm.employees e1;

Query Selection Guidelines: While any subquery can theoretically be replaced with a join, joins generally perform better. Choose based on specific requirements and execution plans.

Table Relationships

Relationship Types

One-to-One: Each record in Table A corresponds to exactly one record in Table B. Example: user accounts and profile information.

One-to-Many: A single record in Table A relates to multiple records in Table B. Example: departments and their employees.

Many-to-Many: Records in both tables can关联 multiple records in the other. Example: students and courses, typically requiring a junction table.

Join Operations

Inner Join: Returns only matching records from both tables.

SELECT e.emp_name, d.dept_name
FROM hrm.employees e
INNER JOIN hrm.departments d ON e.dept_id = d.dept_id;

-- Alternative syntax
SELECT e.emp_name, d.dept_name
FROM hrm.employees e, hrm.departments d
WHERE e.dept_id = d.dept_id;

Outer Join: Returns all records from one table plus matching records from the other.

-- Left outer join: all employees, including those without departments
SELECT e.emp_name, d.dept_name
FROM hrm.employees e
LEFT OUTER JOIN hrm.departments d ON e.dept_id = d.dept_id;

-- Right outer join: all departments, including those without employees
SELECT e.emp_name, d.dept_name
FROM hrm.employees e
RIGHT OUTER JOIN hrm.departments d ON e.dept_id = d.dept_id;

Important: Always specify join conditions. Omitting them produces a Cartesian product, combining every row from one table with every row from another—a common performance and logic error.

Choosing Between Subqueries and Joins