Default Value Control for table-level columns in Oracle

-- 1. Create the test table createtabletab_default (idvarchar2 (2), default_sysdatedate, default_char_datevarchar2 (8), default_guidvar

-- 1. create test table tab_default (id varchar2 (2), default_sysdate date, default_char_date varchar2 (8), default_guid var

1. Script and test procedure

-- 1. Create a test table
Create table tab_default (id varchar2 (2), default_sysdate date, default_char_date varchar2 (8), default_guid varchar2 (100 ));
-- 2. Add the default value Constraint
Alter table tab_default modify default_sysdate date default sysdate;
Alter table tab_default modify default_char_date VARCHAR2 (100) default to_char (sysdate, 'yyyymmdd ');
Alter table tab_default modify default_guid VARCHAR2 (100) default sys_guid ();
-- 3. Test Data insertion
Insert into tab_default (id)
Select '1' from dual;
Commit;
Insert into tab_default (id)
Select '2' from dual;
Commit;
Insert into tab_default (id)
Select '3' from dual;
Commit;
-- 4. view results
Select * from tab_default;

2. Notes

-- 1. Default Value Method
-- It cannot be as follows
Insert into tab_default (id, default_sysdate)
Select '1', null from dual;
-- It can only be as follows (that is, when inserting data, you only need to list the fields to be inserted, and you need to ignore the default field)
Insert into tab_default (id)
Select '1' from dual;
-- 2. You can also assign the default value when creating a table.
Drop table tab_default;
Create table tab_default (col_date date DEFAULT sysdate );