A database constraint is an SQL rule applied to a table column to keep data accurate, reliable, and consistent. Constraints turn design requirements into rules the database enforces, even when application code does not follow the planned design. You can establish them while creating a table or add them afterward. section 6.9
Choose a constraint by asking what must remain true about the data:
- Primary key: identifies each record uniquely. section 8.11
- Foreign key: connects an attribute to a key in another table. section 8.11
- NOT NULL: requires a value for an attribute. section 8.11
- UNIQUE: prevents duplicate values where uniqueness matters. section 8.11
- CHECK: limits values to an explicit condition, such as a nonnegative price. section 8.11
Applying constraints to a user table
Start with the table design, then add rules that protect it. For example: CREATE TABLE user ( email VARCHAR(320) PRIMARY KEY, username VARCHAR(30), password VARCHAR(20), first_name VARCHAR(50), last_name VARCHAR(50), phone_number VARCHAR(15), last_login_time TIMESTAMP ); Here, email identifies each user through a primary key. In a larger design, requirement analysis might also make a name NOT NULL, an email UNIQUE, or an attribute a foreign key referencing a related table. Review the requirements and relationships before selecting each rule. section 8.11
Constraints belong to implementation and testing. After applying them, test expected database operations and fix bugs so the implemented design meets its requirements. section 3.11
