PL/SQL DEFAULT Constraint

In PL/SQL the DEFAULT constraint is used to automatically assign a default value to a column when an explicit value is not provided during the insertion of a new row. This feature is particularly useful for ensuring that columns always have a meaningful value even when the user or application provides none.

In this article, we will explore the concept of DEFAULT constraints in PL/SQL, providing detailed explanations and examples to illustrate their usage and benefits.

PL/SQL DEFAULT Constraint

The DEFAULT constraint specifies a default value for a column in a table. When a new row is inserted into the table, and a value is not provided for a column with a DEFAULT constraint, the database automatically assigns the predefined default value to that column.

This ensures that the column will always contain a valid value, even if the user or application does not provide one during data insertion.

Syntax:

The syntax for defining a DEFAULT constraint is

column_name data_type [DEFAULT default_value]

Where:

• column_name is the name of the column for which the default value is being set.
  
• data_type is the data type of the column (e.g., NUMBER, VARCHAR2, DATE).
  
• default_value is the value to be automatically assigned if no value is provided during an insert operation.

Examples of Using the DEFAULT Constraint

In this section, we'll examine practical examples of how the DEFAULT constraint is applied in PL/SQL. These examples will demonstrate how to automatically assign default values to columns when no explicit value is provided during data insertion.

Example 1: Default Value for Numeric Columns

Suppose we have a table named employees where we want the bonus column to default to a value of 1000 if no value is provided. This ensures that every employee gets a bonus, even if it's not specified during data insertion.

Table Creation:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    bonus NUMBER DEFAULT 1000
);

Inserting Data

INSERT INTO employees (emp_id, emp_name) VALUES (1, 'John Doe');

Output:

Explanation:

In this example, we inserted a row into the employees table without specifying a value for the bonus column. Because of the DEFAULT constraint, the bonus column automatically received the value 1000. This feature helps ensure that even if the user forgets to enter a bonus value, the database maintains a meaningful value.

Example 2: Default Value for String Columns

Now, let’s consider a products table where the status column should default to 'available' if no status is provided. This is useful for inventory systems where most products are generally available unless specified otherwise.

Table Creation:

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    status VARCHAR2(20) DEFAULT 'available'
);

Inserting Data

INSERT INTO products (product_id, product_name) VALUES (101, 'Laptop');

Output:

Explanation:

Here, we inserted a new product into the products table without providing a status. The DEFAULT constraint automatically set the status to 'available'. This approach simplifies data entry by reducing the need to repeatedly specify a common value.

Examples Using the NOT Operator with DEFAULT

The NOT operator can be used in conjunction with the DEFAULT constraint in queries to retrieve rows where a column does not have the default value.

This can be helpful for identifying records that deviate from the expected norm.

Example 1:

Suppose we have an employees table where the bonus column is set with a default value of 1000. This means that any time a new record is inserted into the table without explicitly specifying a value for the bonus column, the value 1000 is automatically assigned to that column.

Now, let's say you want to identify all employees who have a bonus that is different from the default value of 1000. You can use the NOT operator (or the != operator, which has the same effect in this context) to filter out these records.

Query:

SELECT * FROM employees
WHERE bonus != 1000;

Output:

Explanation:

This query will return all records where the bonus column has a value other than the default 1000. These are the rows where the bonus has been explicitly set to a different amount, which may indicate special cases or exceptions.

Example 2:

Consider a products table where the status column has a default value of 'available'. This setup ensures that when a new product is added to the table without explicitly setting the status, it will automatically be marked as 'available'.

However, we might want to identify products that have a status different from 'available', such as products that are sold out, discontinued, or not yet released. This can be done using a query that employs the NOT operator or the != operator to filter out these records.

Let's assume the following data is present in the products table:

Query:

SELECT * FROM productsWHERE status != 'available';

Output:

Explanation:

This query retrieves products whose status is not 'available'. It’s useful for monitoring inventory to identify products that are either sold out or not yet available.

Conclusion

The DEFAULT constraint in PL/SQL is a powerful tool for ensuring that columns have meaningful default values, which helps maintain data integrity and simplifies data entry. By using default values, you can ensure that your database columns always have valid data, even if the application does not explicitly provide it.