In Oracle SQL, how can I create table with a rule for one value to default to a percentage of anothe-CodePudding

How can I change the CREATE TABLE block so that 'commission' will default to a percentage of 'price'?

For example, is there a way to have 'commission' automatically be a 5% of 'price' when doing an INSERT and entering NULL for commission?

CREATE TABLE SALE (
    price NUMBER (5, 2),
    commission NUMBER (5, 2)
);

INSERT INTO SALE (price, commission)
    VALUES(1000, NULL)
;

CodePudding user response：

You can not create table with a default value of another column

Restrictions on Default Column Values Default column values are subject to the following restrictions:

A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The expression can be of any form except a scalar subquery expression.

ORACLE documentation

You can create a trigger for the INSERT. But that is another question. And Stack Overflow has a rule of not asking multiple questions in one post.