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 :
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