Constraints Overview:
- Definition: Constraints are rules used for optimizing databases and ensuring data integrity during updates, deletes, or inserts into a table.
- Purpose: Maintain data integrity by setting limits on data or types of data that can be manipulated in a table.
Types of Constraints:
1. NOT NULL:
- Purpose: Ensures a column cannot have NULL values.
- Example: If a column has a NOT NULL constraint, it means you cannot leave it empty when adding a new record.
2. UNIQUE:
- Purpose: Requires a column or set of columns to have unique values.
- Example: If a column has a UNIQUE constraint, no two records in the table can have the same value in that column.
3. DEFAULT:
- Purpose: Provides a default value to a column if none is specified during an insert.
- Example: If a DEFAULT constraint is set for a column with a default value of 0, if no value is provided during an insert, it will automatically be set to 0.
4. CHECK:
- Purpose: Defines a range of acceptable values for a column.
- Example: If a CHECK constraint is set for a column with a range of 1 to 100, any value outside this range will be rejected.
5. Key Constraints:
a. Primary Key:
- Purpose: Uniquely identifies each record in a table.
- Conditions: Must have unique values and cannot contain NULL.
- Example: Social Security Number in an employee database.
b. Foreign Key:
- Purpose: Establishes a link between tables by referencing the primary key of another table.
- Example: If there’s a foreign key in an “Orders” table pointing to the “CustomerID” in a “Customers” table, it creates a relationship between them.
6. Domain Constraints:
- Purpose: Enforces the data type for each column.
- Example: If a column is set to store only integers, it won’t accept values like text or dates.