Add a column to a table with check constraint SQL

sql oracle11g constraints alter
Next_Line_Nick picture Next_Line_Nick · Feb 4, 2014 · Viewed 10k times · Source

I want to add a column to a table, then add a check constraint to make sure its greater than 0. I cant seem to get this to run in oracle sl developer. 

Alter TABLE store101
add column Base_salary Number(7,2)
constraint store101_Base_salary_ck
check (Base_salary > 0);

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

Answer

peterm picture peterm · Feb 4, 2014

There is no ADD COLUMN clause in ALTER TABLE syntax. It's just ADD. 

ALTER TABLE store101
ADD Base_salary NUMBER(7, 2) -- there is no need to add COLUMN clause
CONSTRAINT store101_Base_salary_ck 
CHECK (Base_salary > 0);

Here is SQLFiddle demo

Related questions

Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
How to add a not null constraint on column containing null values

I have a table with a column that contains a few null values. I want to add a NOT NULL constraint on that column without updating the existing nulls to a non-null value. I want to keep the existing null …

Read More
How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More