Home » SQL & PL/SQL » SQL & PL/SQL » dropping default constraint
dropping default constraint [message #157293] Wed, 01 February 2006 07:49 Go to next message
lijok
Messages: 68
Registered: April 2005
Member
Hi,
Can some one help me in getting the right syntax for dropping default constraint on a field?

The constraint was added this way
ALTER TABLE ICITEM ADD ITEMTYPE CHAR(1) DEFAULT 'I'

When I try this I get the following error, I tried to look through google but did not get much information on this.

SQL> ALTER TABLE ICITEM MODIFY ITEMTYPE DROP DEFAULT
  2  /
ALTER TABLE ICITEM MODIFY ITEMTYPE DROP DEFAULT
                                   *
ERROR at line 1:
ORA-00902: invalid datatype
Re: dropping default constraint [message #157299 is a reply to message #157293] Wed, 01 February 2006 08:00 Go to previous messageGo to next message
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
Member
You want to allow any values on that field, nulls included. So, why don't
ALTER TABLE ICITEM MODIFY ITEMTYPE DEFAULT NULL;

Re: dropping default constraint [message #157300 is a reply to message #157293] Wed, 01 February 2006 08:04 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Link.
Re: dropping default constraint [message #157301 is a reply to message #157293] Wed, 01 February 2006 08:09 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
If you cared to look in the sticky, you would have found a link to the Oracle manuals. Especially the Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 might have been interesting, as it explains in Chapter 11 (SQL Statements: ALTER TABLE to ALTER TABLESPACE) what you are looking for.

From the FM

To discontinue previously specified default values, so that they are no longer automatically inserted into newly added rows, replace the values with nulls, as shown in this statement:

ALTER TABLE product_information
   MODIFY (min_price DEFAULT NULL);




MHE

EDIT: I'm getting slow Razz

[Updated on: Wed, 01 February 2006 08:10]

Report message to a moderator

Re: dropping default constraint [message #157302 is a reply to message #157301] Wed, 01 February 2006 08:19 Go to previous message
lijok
Messages: 68
Registered: April 2005
Member
Thanks a lot
Previous Topic: E-mail validation using regular expression
Next Topic: Archiving tables in Oracle
Goto Forum:
  


Current Time: Sun Aug 24 17:02:32 CDT 2025