| Simply defined, constraints are rules that a database designer specifies when setting up a table. MySQL enforces these rules when changes are made to information stored in the database. These changes usually occur via INSERT, UPDATE, or DELETE statements, although they can also be triggered by structural alterations to the tables themselves. MySQL offers the following constraints: UNIQUE Guarantees that there will be no duplicate values in a column PRIMARY KEY Identifies the primary unique identifier of a row FOREIGN KEY Codifies and enforces the relationships among two or more tables with regard to appropriate behavior when data changes DEFAULT Provides an automatic value for a column if a user omits entering data NOT NULL Forces users to provide information for a column when inserting or updating data ENUM Allows you to set a restricted list of values for a particular column, although it is not a true constraint SET Allows you to store combinations of predefined values within a string column, although it is not a true constraint
Constraints benefit your organization in several ways, including the following: Data consistency/organizational policy enforcement As a centralized set of rules that are processed for all changes by all users to a given table, constraints greatly reduce the chances that someone will mistakenly introduce a data integrity problem. This can also help implement your organization's operational policies. For example, your organization might not allow a customer to be created without a valid phone number. A NOT NULL constraint on the relevant column in the appropriate table(s) blocks any invalid entries into the database. Performance Constraints run on the database server. In most cases, this is faster than manually coding and downloading (or installing) the same logic on a client. Developer time and productivity Reuse is a foundation of good software design practices. By using constraints, you are reducing the amount of time that developers need for these types of tasks, as well as helping cut down on potential errors. This lets the developers spend their valuable time building the core value of their applications.
The following sections look at each of these constraints to see how you can use them to achieve the benefits listed previously. To keep the examples consistent, we refer to the challenges faced by the designers of High-Hat Airways' new lost luggage application. |
