How to delete Not null constraint in SQL?

sql oracle oracle-sqldeveloper

12,150

Solution 1

Try

ALTER TABLE EMP MODIFY (ENO null);

Solution 2

You can drop the constraint using Sachu's answer (which BTW I don't think deserved a downvote).

To disable the constraint you first need to find its name, which is generated by Oracle. You can find the name in Oracle's USER_CONSTRAINTS view: look for the one where the "search condition" is "ENO" IS NOT NULL" -- in your question it will be the only constraint in the table but in other cases there may be multiple constraints on the table (or even on the column).

SQL> CREATE TABLE EMP
   2 (
   3 ENO     NUMBER(5, 0) not null,
   4 ENAME   VARCHAR2(20 BYTE),
   5 SAl     NUMBER(10, 0),
   6 DPTNAME VARCHAR2(50 BYTE),
   7 EPLACE  VARCHAR2(20 BYTE),
   8 DOB     DATE
   9 );

Table created.

SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'EMP';

CONSTRAINT_NAME SEARCH_CONDITION
--------------- -----------------
SYS_C009208     "ENO" IS NOT NULL

So the name Oracle gave the constraint was SYS_C009208. Now you can disable it:

SQL> ALTER TABLE EMP DISABLE CONSTRAINT SYS_C009208;

Table altered.

12,150

Author by

Admin

Updated on June 13, 2022

Comments

Admin almost 2 years
I just created a table like below:
```
CREATE TABLE EMP 
  ( 
     ENO     NUMBER(5, 0) not null, 
     ENAME   VARCHAR2(20 BYTE), 
     SAl     NUMBER(10, 0), 
     DPTNAME VARCHAR2(50 BYTE), 
     EPLACE  VARCHAR2(20 BYTE), 
     DOB     DATE 
  );
```
Now I want to disable that NOT NULL constraint. I tried with ALTER TABLE EMP MODIFY (ENO NOT NULL DISABLE);, but it showing some error.

Could you please suggest me where I'm going wrong?
Admin almost 9 years

works exactly. can You tell me how to delete only column data not column name?
Sachu almost 9 years

didn't understand u mean u need to delete all data in a particular column or make it null?
Sachu almost 9 years

@downvoter can u tell me what the issue with this answer..it works for me
Admin almost 9 years

ya I need all data under a particular column
Sachu almost 9 years

Update EMP set ENO = null if you don't want to check any condtion else put the condition along with it
Sachu almost 9 years

@Downvoter if there is a reason behind this downvote please let me know..it will be useful for me also
Sachu almost 9 years

@RamBM data got deleted?
Lalit Kumar B almost 9 years

You are confusing NOT NULL constraint with all other constraints. In Oracle, NOT NULL is the ONLY constraint you could remove without knowing the constraint name.For all other constraints you need to know the constraint name.
Admin almost 9 years

@Sanchu. Ya got deleted.. thank you
Admin almost 9 years

ya marked buddy@Sachu
Sachu almost 9 years

@RamBM you are welcome bro
Ed Gibbs almost 9 years

You can also drop a PK constraint without knowing its name: ALTER TABLE myTable DROP PRIMARY KEY. That aside, I'm not showing how to drop the constraint here; I'm showing how to disable it (the OP used "drop" in the title but "disable" in the question). There's a big difference because you can disable the NOT NULL constraint, add null column values, then re-enable the constraint with ENABLE NOVALIDATE and the existing nulls are allowed. You can't do that with drop/re-add. Granted, you probably wouldn't want to do that with the posted table structure :)
Lalit Kumar B almost 9 years

Fair enough, OP says DELETE(which would mean DROP) in the title, while DISABLE in the question.