How to delete Not null constraint in SQL?

Question

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?

`but it showing some error`: what's the error? Is `ENO` a FK? Did you try something like `ALTER TABLE EMP MODIFY (ENO NULL);`? — Bruno Ribeiro, Jul 08 '15 at 01:08

score 4 · Answer 1 · answered Jul 08 '15 at 01:21

4

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.

answered Jul 08 '15 at 01:21

Ed Gibbs

25,924
4
46
69

1

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. – Lalit Kumar B Jul 08 '15 at 04:45
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 :) – Ed Gibbs Jul 08 '15 at 22:09
Fair enough, OP says DELETE(which would mean DROP) in the title, while DISABLE in the question. – Lalit Kumar B Jul 09 '15 at 03:25

score 2 · Accepted Answer · answered Jul 08 '15 at 01:09

2

Try

ALTER TABLE EMP MODIFY (ENO null);

answered Jul 08 '15 at 01:09

Sachu

7,555
7
55
94

works exactly. can You tell me how to delete only column data not column name? – Ram BM Jul 08 '15 at 01:11
didn't understand u mean u need to delete all data in a particular column or make it null? – Sachu Jul 08 '15 at 01:12
@downvoter can u tell me what the issue with this answer..it works for me – Sachu Jul 08 '15 at 01:13
ya I need all data under a particular column – Ram BM Jul 08 '15 at 01:15
`Update EMP set ENO = null` if you don't want to check any condtion else put the condition along with it – Sachu Jul 08 '15 at 01:17
@Downvoter if there is a reason behind this downvote please let me know..it will be useful for me also – Sachu Jul 08 '15 at 01:17
@RamBM data got deleted? – Sachu Jul 08 '15 at 01:35
@Sanchu. Ya got deleted.. thank you – Ram BM Jul 08 '15 at 08:42
ya marked buddy@Sachu – Ram BM Jul 08 '15 at 08:53
@RamBM you are welcome bro – Sachu Jul 08 '15 at 08:55

How to delete Not null constraint in SQL?

2 Answers2