How to Add Default Constraint Using Alter Table in Oracle
Adding default constraints to a table in Oracle is a crucial step in ensuring data integrity and consistency. Default constraints automatically assign a default value to a column when no value is specified during an insert operation. This article will guide you through the process of adding a default constraint to an existing table using the ALTER TABLE statement in Oracle.
Understanding Default Constraints
Before diving into the specifics of adding a default constraint, it’s essential to understand what a default constraint is and how it works. A default constraint is a type of constraint that specifies a default value for a column if no value is provided during an insert operation. This default value is applied automatically by the database system.
Prerequisites for Adding a Default Constraint
Before you can add a default constraint to a table, you must ensure that the following prerequisites are met:
1. You have the necessary privileges to alter the table structure.
2. The column to which you want to add the default constraint must not already have a default constraint or a not null constraint.
3. The default value must be compatible with the data type of the column.
Adding a Default Constraint Using ALTER TABLE
To add a default constraint to a table in Oracle, follow these steps:
1. Identify the table and column to which you want to add the default constraint.
2. Determine the default value you want to assign to the column.
3. Use the ALTER TABLE statement to add the default constraint.
Here’s an example of how to add a default constraint to a column named “status” in a table called “employees”:
“`sql
ALTER TABLE employees
ADD CONSTRAINT default_status
DEFAULT ‘Active’ FOR status;
“`
In this example, the default value ‘Active’ is assigned to the “status” column whenever a new row is inserted without specifying a value for the “status” column.
Verifying the Default Constraint
After adding the default constraint, it’s essential to verify that it has been applied correctly. You can do this by querying the database dictionary views or by inserting a new row without specifying a value for the “status” column and checking if the default value is assigned.
“`sql
INSERT INTO employees (name, department) VALUES (‘John Doe’, ‘HR’);
SELECT FROM employees;
“`
The output should show that the “status” column has been assigned the default value ‘Active’.
Conclusion
Adding a default constraint to a table in Oracle is a straightforward process that can help maintain data integrity and consistency. By following the steps outlined in this article, you can easily add a default constraint to an existing table and ensure that default values are automatically assigned to columns when necessary.
