How to alter table with not null constraint

P: 8 How to add not null constrint to an existing table? i tried with alter table emp add constraint not_null_name not null(name) command but it's giving error. alter table emp add constraint not_null_name not null(name) * ERROR at line 1: ORA-00904: : invalid identifier Share this Question Expert 5 K+ P: 8,127 NOT NULL constraint can't be added at table level to add the constraint u need to modify the column alter table emp modify ename not null; P: 78 Hi, If u need add any constraints to tables means you write like alter table table_name add constraints if u need add not null constraints means alter table table_name modify constraints P: n/a hi why NOT NULL is only constraint which cannot be enforced at table level? Expert 100+ P: 2,368 You can do so by using below ALTER statement: ALTER TABLE emp MODIFY name NOT NULL / Make sure there are no existing NULL values in the column name, else you will not be able to ENABLE NOT NULL constraint. Check below: SQL> create table my_test(col1 NUMBER Table created. SQL> ed Wrote file afiedt.buf  1* insert into my_test values(1) SQL> / 1 row created. SQL> ed Wrote file afiedt.buf  1* insert into my_test values( NULL) SQL> / 1 row created. SQL> commit; Commit complete. SQL> alter table my_ Test modify col1 NOT NULL; alter table my_ Test modify col1 NOT NULL * ERROR at line 1: ORA-02296: cannot enable ( APPS.) - null values found SQL> delete from my_test WHERE col1 IS NULL; 1 row deleted. SQL> COMMIT; Commit complete. SQL> alter table my_ Test modify col1 NOT NULL; Table altered. SQL> desc my_test;  Name Null? Type COL NOT NULL NUMBER SQL>  P: 1 add constraint only for out-of-line constraints while not-null constraint is inline constraint, so cannot use add constraint for not-null constraint ie. to add inline constraint uses modify column alter table emp modify.
Name ALTER TABLE - change the definition of a table Synopsis ALTER TABLE [ ONLY ] name [ * ] action [. ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name ALTER TABLE name SET SCHEMA new_schema where action is one of: ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [. ] ] DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] ALTER [ COLUMN ] column SET DEFAULT expression ALTER [ COLUMN ] column DROP DEFAULT ALTER [ COLUMN ] column SET | DROP NOT NULL ALTER [ COLUMN ] column SET STATISTICS integer ALTER [ COLUMN ] column SET ( attribute_option = value [. ] ) ALTER [ COLUMN ] column RESET ( attribute_option [. ] ) ALTER [ COLUMN ] column SET STORAGE PLAIN | EXTERNAL | EXTENDED | MAIN ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name DISABLE RULE rewrite_rule_name ENABLE RULE rewrite_rule_name ENABLE REPLICA RULE rewrite_rule_name ENABLE ALWAYS RULE rewrite_rule_name CLUSTER ON index_name SET WITHOUT CLUSTER SET WITH OIDS SET WITHOUT OIDS SET ( storage_parameter = value [. ] ) RESET ( storage_parameter [. ] ) INHERIT parent_table NO INHERIT parent_table OF type_name NOT OF OWNER TO new_owner SET TABLESPACE new_tablespace and table_constraint_using_index is: [ CONSTRAINT constraint_name ] UNIQUE | PRIMARY KEY USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] Description ALTER TABLE changes the definition of an existing table. There are several.
I've setup a table in SQL Server 2008 Express and forgot to add a not null constraint to my unique recordid column. I tried to add it afterward, with this statement: alter table movie_archive alter column Record ID Not null; but it gives me an error message, saying there's a syntax error at not. What am I doing wrong?.