Adding a default constraint in Oracle while altering a column

Question

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?

Answer 1

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

• Manngo: I was also able to do this: MODIFY quantity DEFAULT 1 NOT NULL, it’s pretty flexible.