oracle set default value

In Oracle database, you can set default values ​​for columns in a table so that column values ​​are automatically populated when new records are inserted. The default value can be a constant, an expression, or a system function. If no column value is provided, the column is automatically populated with a default value.

In actual database applications, setting default values ​​can improve data input efficiency, reduce errors and incomplete data, and make the database structure more stable.

This article will introduce how to set default values ​​in Oracle database.

1. Set default values ​​when creating a table

When creating a table, you can use the DEFAULT keyword to set default values ​​for columns. For example:

CREATE TABLE employee (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2) DEFAULT 0
);

In the above example, the default value of the hire_date column is the current date, and the default value of the salary column is 0.

2. Modify the default values ​​of the columns in the table

After creating the table, you can use the ALTER TABLE statement to modify the default values ​​of the columns in the table.

For example, to modify the default value of the salary column in the employee table:

ALTER TABLE employee MODIFY salary DEFAULT 5000;

In the above example, modify the default value of the salary column to 5000.

3. Delete the default value of a column in a table

If you need to delete the default value of a column in the table, you can use the ALTER TABLE statement and specify the default value as NULL.

For example, to delete the default value of the salary column in the employee table:

ALTER TABLE employee MODIFY salary DEFAULT NULL;

In the above example, delete the default value of the salary column.

It should be noted that if the column already has data, deleting the default value will not affect the value of the existing data. Only newly inserted data will use NULL values ​​or new default values.

4. Restrictions on the default value

When setting the default value, you need to pay attention to the following restrictions:

• The default value must be consistent with The data type of the column matches;
• The default value must be a constant, an expression or a system function;
• If an expression or function is used as the default value, it must be ensured that it does not depend on The values ​​of other columns, otherwise a syntax error will occur;
• If a composite primary key is set, the default value cannot depend on those columns.

5. Summary

Setting default values ​​is a simple and useful technique that is used very frequently in Oracle databases. This article describes how to modify table columns and delete table column default values ​​when creating a table, and explains some restrictions on default value settings.

For database applications that require a large amount of data input, setting default values ​​can not only improve data input efficiency, but also reduce data errors and missing data, making the database structure more stable.

The above is the detailed content of oracle set default value. For more information, please follow other related articles on the PHP Chinese website!