0

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?

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.