Adding a default constraint in Oracle while altering a column

alter-tabledefault valueoracle

I'm new to Oracle. I need to port a SQL Server command to Oracle.

I want to alter a column to add a new constraint with a default value.

SQL Server command

ALTER TABLE <schema_name>.<table_name> 
ADD CONSTRAINT [<constraint_name>]
DEFAULT (1) FOR [<column_name>]

Oracle command

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> 
DEFAULT (1) FOR <column_name>

I get the following error when running the Oracle query:

Error report - 
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

It might be because FOR is an invalid identifier?

But then how do I add a constraint with a default value for a specific column?

Do we need to alter a column and set the default value?

Which is the right way to do this?

Best Answer

A default value is not a constraint in Oracle. You simply alter the column as:

SQL> create table tab1 (col1 number, col2 number);

Table created.

SQL> alter table tab1 modify (col2 default 1);

Table altered.

SQL> select * from user_constraints;

no rows selected

SQL> insert into tab1 (col1) values (0);

1 row created.

SQL> select * from tab1;

      COL1       COL2
---------- ----------
         0          1
Related Question