Chapter 3: Manipulate computerised database

3.1 Database business rules

A business rule is a statement of how an organisation works, such as "every repair job belongs to a customer" or "a repair charge can never be negative". When the rules are stored in the database itself, as constraints, the DBMS refuses any data that breaks them, whichever program or user enters it. Rules that are checked only in an application screen can be bypassed by another program, an import file or a direct SQL statement, which is why the important ones are written into the database design.

Example: a bicycle repair shop applies these rules. Each customer has a phone number that no other customer has. A repair job must belong to an existing customer. A job charge cannot be negative. A job is Open until it is marked otherwise. Each of these rules becomes a constraint, as the table shows.

The examples in this chapter use one small sample database for a bicycle repair shop. The examples are written for MySQL 8.0, which reached end of life in April 2026 (MySQL 8.4 is the current long-term support release), and use standard SQL, except where the text says that a feature is specific to MySQL. Building the database is covered in the chapter Create database system, so the script is given here only so that every example can be run. Run it in an SQL editor. In MySQL Workbench 8.0 choose File > New Query Tab, paste the script and click the lightning bolt button; Oracle replaced 8.0 with a rebuilt MySQL Workbench 26 in September 2026, so menu names in the newer version may differ.

CREATE DATABASE repair_shop;
USE repair_shop;

CREATE TABLE customer (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name   VARCHAR(60) NOT NULL,
  phone       VARCHAR(15) NOT NULL,
  town        VARCHAR(30),
  CONSTRAINT uq_customer_phone UNIQUE (phone)
);

CREATE TABLE part (
  part_code   VARCHAR(10) PRIMARY KEY,
  part_name   VARCHAR(40) NOT NULL,
  unit_price  DECIMAL(8,2) NOT NULL CHECK (unit_price >= 0),
  stock_qty   INT NOT NULL DEFAULT 0
);

CREATE TABLE repair_job (
  job_id      INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  job_date    DATE NOT NULL,
  description VARCHAR(100) NOT NULL,
  charge      DECIMAL(8,2) NOT NULL CHECK (charge >= 0),
  status      VARCHAR(10) NOT NULL DEFAULT 'Open',
  CONSTRAINT fk_job_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
);

CREATE TABLE job_part (
  job_part_id INT AUTO_INCREMENT PRIMARY KEY,
  job_id      INT NOT NULL,
  part_code   VARCHAR(10) NOT NULL,
  quantity    INT NOT NULL CHECK (quantity > 0),
  CONSTRAINT uq_job_part UNIQUE (job_id, part_code),
  CONSTRAINT fk_jp_job FOREIGN KEY (job_id) REFERENCES repair_job (job_id),
  CONSTRAINT fk_jp_part FOREIGN KEY (part_code) REFERENCES part (part_code)
);

The four tables are customer, part, repair_job and job_part, which records the parts used on each job. A CHECK constraint is enforced from MySQL 8.0.16; in earlier versions MySQL read it and ignored it.

3.1.1 Unique constraints

A unique constraint states that no two rows of a table may hold the same value in a column, or the same combination of values in a group of columns. The DBMS builds an index to check each new value and refuses an INSERT or UPDATE that would create a duplicate. A primary key is also unique, but the two differ: a table has only one primary key and its columns cannot be empty, while a table may have many unique constraints.

In the customer table the constraint uq_customer_phone makes sure that one phone number belongs to one customer. A second customer with the same number is refused.

INSERT INTO customer (full_name, phone, town)
VALUES ('Test Person', '0700000101', 'Nakuru');

If the number 0700000101 is already stored, MySQL answers with an error like this one and stores nothing:

ERROR 1062 (23000): Duplicate entry '0700000101' for key 'customer.uq_customer_phone'

A unique constraint can cover several columns together. The constraint uq_job_part on job_part (job_id, part_code) allows the same part on many jobs and many parts on one job, but it forbids listing the same part twice on the same job, so a second line for the same part must update the quantity instead.

A unique constraint can be added to a table that already exists, and removed when the rule no longer applies. In MySQL the constraint is dropped with DROP INDEX, because it is implemented as an index.

ALTER TABLE customer ADD CONSTRAINT uq_customer_phone UNIQUE (phone);
ALTER TABLE customer DROP INDEX uq_customer_phone;

The ADD statement fails if the table already holds duplicates, so the duplicates are searched for first.

SELECT phone, COUNT(*) AS times_used
FROM customer
GROUP BY phone
HAVING COUNT(*) > 1;

Every phone number returned by this query must be corrected or removed before the constraint can be added.

Guidelines for using unique constraints:

• Choose natural identifiers: Use a unique constraint for values that are meant to be distinct, such as an email address, a national identity number or an item code.
• Name the constraint: A name such as uq_customer_phone makes the error message and later maintenance easier to read.
• Combine columns when needed: Use a composite constraint when only the combination must be distinct.
• Handle the error in the application: Show the user a clear message such as "This phone number is already registered" instead of the raw database error.

3.1.2 Referential integrity

Referential integrity is the rule that a foreign key value must match an existing primary key value in the parent table, or be NULL where the column allows it. It stops orphan records: rows that refer to a parent that does not exist. In the sample database repair_job.customer_id refers to customer.customer_id, so every job must belong to a customer that is in the customer table.

The DBMS enforces the rule in two directions:

• Inserting or updating a child row: The foreign key value must exist in the parent table.
• Deleting or updating a parent row: The DBMS checks whether child rows still refer to it and acts according to the rule chosen for the foreign key.

A job for a customer number that does not exist is refused.

INSERT INTO repair_job (customer_id, job_date, description, charge)
VALUES (99, '2026-03-10', 'Puncture repair', 200.00);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`repair_shop`.`repair_job`, CONSTRAINT `fk_job_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`))

Deleting a customer who still has jobs is refused in the same way, with error 1451, because the jobs would be left without a parent:

DELETE FROM customer WHERE customer_id = 1;

The rule chosen for the parent row is set with the ON DELETE and ON UPDATE options of the foreign key.

The rule for job_part can be changed so that the part lines of a job are removed when the job is deleted:

ALTER TABLE job_part DROP FOREIGN KEY fk_jp_job;
ALTER TABLE job_part
  ADD CONSTRAINT fk_jp_job FOREIGN KEY (job_id)
  REFERENCES repair_job (job_id) ON DELETE CASCADE;

Referential integrity also decides the order of work. Rows are inserted parent first: the customer before the job, and the job before its parts. They are deleted child first: the parts of a job, then the job, then the customer. Foreign keys are enforced by the InnoDB storage engine, which is the default in MySQL 8.0; tables in the older MyISAM engine accept foreign key definitions but ignore them.