Re: Drop 'NOT NULL' Constraint
The command that worked was
ALTER TABLE table MODIFY column NULL;
This removed the NULL constraint but did not affect the data in the
column.
Chris
Deshpande, Kirti wrote:
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
-
CANOT 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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Chris Rezek
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Drop 'NOT NULL' Constraint
On Wed, 23 May 2001, Chris Rezek wrote: 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? alter table foo modify (bar null); Cheers, GC -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gregory Conron INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Drop 'NOT NULL' Constraint
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
-
CANOT 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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
