Data integrity means keeping data accurate, complete, consistent, and trustworthy throughout its storage and use. In database design, it protects relationships and prevents unintended information loss. section 3.3 section 7.3
Build integrity into the database by checking these design decisions:
- Give every column an appropriate data type. section 7.3
- Give every table a primary key. section 7.3
- Use foreign keys to preserve relationships between tables. section 7.3
- Base constraints on analyzed requirements. section 7.3
These measures keep values consistent across tables, reduce redundancy, and prevent anomalies. An insertion anomaly forces unrelated data into a new record; an update anomaly creates conflicting values; a delete anomaly unintentionally removes information. section 3.3
Connecting authors and books
Suppose a publishing application stores authors and books in related tables. A book table with author_id but no foreign key can contain an orphan record that refers to a nonexistent author. Add a constraint so author_id must reference author(author_id). section 7.3
CREATE TABLE book (
book_id INT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT NOT NULL,
CONSTRAINT FK_author_id
FOREIGN KEY (author_id)
REFERENCES author(author_id)
);
Inspect the corrected SQL definition, which adds a foreign key from book.author_id to author.author_id. section 7.3
Review integrity repeatedly during database design and implementation. Small omissions, such as forgetting a foreign key, can make a database harder to manage and secure. section 3.16 section 7.3
