Integrity Constraints:
Integrity constraints are rules defined on a database schema that enforce the consistency and accuracy of data in a relational database. These constraints help maintain the quality of the data and prevent the database from entering into inconsistent states. There are several types of integrity constraints:
- Entity Integrity Constraint:
- Ensures that a primary key cannot have a NULL value. It guarantees the uniqueness and non-null status of the primary key attribute.
- Referential Integrity Constraint:
- Enforces relationships between tables through foreign key constraints. It ensures that values in a foreign key column match the values in the corresponding primary key column of another table.
- Domain Integrity Constraint:
- Enforces the validity of data based on a specific domain. It ensures that values stored in a column meet a specific set of criteria, such as data type, format, or range.
- Unique Constraint:
- Ensures that values in a column (or a combination of columns) are unique across all rows in a table. It enforces uniqueness but allows NULL values.
- Default Constraint:
- Specifies a default value for a column. If a user does not provide a value when inserting a new record, the default value is used.
Domain Constraints:
Domain constraints are a specific type of integrity constraint related to the definition and validation of data types and values within a specific domain. They ensure that the values entered into a column conform to the defined data type and constraints. Domain constraints are typically defined during the creation of a table and can include specifications such as:
- Data Type:
- Specifies the type of data that can be stored in a column, such as INTEGER, VARCHAR, DATE, etc.
- Length Constraint:
- Limits the length of character strings that can be stored in a column.
- Range Constraint:
- Defines a range of acceptable numeric values for a column.
- Format Constraint:
- Specifies a specific format that data in a column must adhere to, such as a date format or a specific pattern for character strings.
- Enumerated Values:
- Limits the possible values for a column to a predefined set, creating a list of valid options.
Example:
Suppose you have a table called "Employees" with the following domain constraints:
- The "Salary" column has a range constraint, ensuring that the salary is between $30,000 and $150,000.
- The "Gender" column is constrained to accept only values 'M' or 'F' as specified in an enumerated list.