CONSTRAINTS in Oracle SQL Part -2 - Day 27
In Oracle SQL, constraints are rules or conditions that are applied to database tables to enforce data integrity and maintain the accuracy, consistency, and reliability of the data. Constraints help define and enforce the business rules and requirements that govern the data stored in the database.
Here are some common types of constraints in Oracle SQL:
1. Primary Key Constraint: A primary key constraint ensures that a column or a combination of columns uniquely identifies each row in a table. It prevents duplicate or null values in the primary key column(s). Only one primary key constraint can be defined per table.
2. Foreign Key Constraint: A foreign key constraint establishes a relationship between two tables based on a common column(s). It ensures referential integrity by enforcing that values in the foreign key column(s) of one table must match the values in the primary key column(s) of another table.
3. Unique Constraint: A unique constraint ensures that the values in a column or a combination of columns are unique within a table. It prevents duplicate entries and maintains data consistency.
4. Check Constraint: A check constraint defines a condition that each row in a table must satisfy. It allows you to specify custom rules for data validation. For example, you can define a check constraint to ensure that a column only contains positive numbers or falls within a specific range.
5. Not Null Constraint: A not null constraint ensures that a column must have a value and cannot be left empty (null). It guarantees that essential data is present and avoids inconsistencies due to missing values.
6. Default Constraint: A default constraint specifies a default value for a column if no value is explicitly provided during an insert operation. It ensures that the column always has a value, even if it is not explicitly specified.
Constraints can be defined at the time of table creation using the `CREATE TABLE` statement or added later using the `ALTER TABLE` statement. They provide a way to enforce data integrity rules and prevent invalid or inconsistent data from being stored in the database.
When a constraint is violated during data manipulation operations such as inserts, updates, or deletes, Oracle SQL raises an error, and the operation is rejected, ensuring that the data remains consistent and accurate.