RE: Drop 'NOT NULL' Constraint

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Wed, 23 May 2001 18:54:11 -0700
Message-ID: <F001.0030CCA1.20010523183524@fatcity.com>

Kevin,
I don't think so. Here is what happens with a 8.1.7 database. SQL> create table t (ca varchar2(2) not null, cb number); Table created.
SQL> insert into t values ('AA', 1);
1 row created.
SQL> commit;
Commit complete.
SQL> desc t

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 CA                                        NOT NULL VARCHAR2(2)
 CB                                                 NUMBER
SQL> select * from t;               
CA              CB
-- ---------------
AA               1

SQL> alter table t modify (ca null);
Table altered.
SQL> desc t

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 CA                                                 VARCHAR2(2)
 CB                                                 NUMBER
SQL> select * from t;
CA              CB
-- ---------------
AA               1

SQL> If the column 'CA' were to have a primary key constraint, then the constraint needs to be dropped to modify the column as a nullable column.

Regards,

Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Kevin Kostyszyn [SMTP:kevin_at_dulcian.com]
> Sent: Wednesday, May 23, 2001 4:57 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Drop 'NOT NULL' Constraint
>
> I think that if there is already data in the column you will not be able
> to
> do that.
>
> -----Original Message-----
> Sent: Wednesday, May 23, 2001 5:15 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I created a table with a 'NOT NULL' constraint on a VARCHAR2 column. I
> would like to remove the NOT NULL constraint. I've tried a couple of
> 'ALTER TABLE' commands but no luck.
>
> How does one do this?
>
> Also:
>
> How does one figure out which generated constraints (from
> user_constraints) do what?
>
> Chris Rezek
> Database Administrator
> DotClick
> http://www.dotclick.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Chris Rezek
> INET: crezek_at_dotclick.com
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed May 23 2001 - 20:54:11 CDT