OraFAQ Forum: SQL & PL/SQL » Need few clarification on Alter table.

Home » SQL & PL/SQL » SQL & PL/SQL » Need few clarification on Alter table. (Oracle 11G, WIN7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need few clarification on Alter table. [message #643720]

Fri, 16 October 2015 05:24

gopieee16
Messages: 28
Registered: May 2011
Location: Chennai

Junior Member

Hello All,

Need few clarification on Alter table script, today while exploring our code base i come across following alter table statement,


ALTER TABLE "RTMS_WEBDB"."APPOINTMENT_SURVEY" MODIFY ("DATE_ENTERED" NOT NULL DISABLE);
ALTER TABLE "RTMS_WEBDB"."APPOINTMENT_SURVEY" MODIFY ("DATE_ENTERED" NOT NULL);

If i use the 1st statement the table description looks like


SQL> desc APPOINTMENT_SURVEY
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 SURVEY_ID                                          NUMBER(11)
 STORE_NUMBER                                       CHAR(6)
 DATE_ENTERED                                       DATE
 OVERALL_SATISFACTION_IND                           CHAR(1)
 OVERALL_SATISFACTION_COMMENT                       VARCHAR2(500)
 STAFF_BEHAVIOR_IND                                 CHAR(1)
 STAFF_BEHAVIOR_COMMENT                             VARCHAR2(500)
 STAFF_KNOWLEDGE_IND                                CHAR(1)
 STAFF_KNOWLEDGE_COMMENT                            VARCHAR2(500)
 FACILITIES_CLEANLINESS_IND                         CHAR(1)
 FACILITIES_CLEANLINESS_COMMENT                     VARCHAR2(500)
 VEHICLE_FIXED_IND                                  CHAR(1)
 VEHICLE_FIXED_COMMENT                              VARCHAR2(500)
 VEHICLE_READINESS_IND                              CHAR(1)
 VEHICLE_READINESS_COMMENT                          VARCHAR2(500)
 APPOINTMENT_SETUP_IND                              CHAR(1)
 APPOINTMENT_SETUP_COMMENT                          VARCHAR2(500)
 CONTINUED_USE_IND                                  CHAR(1)
 CONTINUED_USE_COMMENT                              VARCHAR2(500)
 EXPECTATIONS_MET_IND                               CHAR(1)
 EXPECTATIONS_MET_COMMENT                           VARCHAR2(500)
 DESIRED_TIME_IND                                   CHAR(1)
 DESIRED_TIME_COMMENT                               VARCHAR2(500)
 SUGGESTED_IMPROVMENTS_COMMENT                      VARCHAR2(500)
 CONTACT_MANAGER                                    CHAR(1)
 CONTACT_MANAGER_PREFERENCE                         VARCHAR2(20)
 FIRST_NAME                                         VARCHAR2(30)
 LAST_NAME                                          VARCHAR2(30)
 STREET_ADDRESS_1                                   VARCHAR2(50)
 STREET_ADDRESS_2                                   VARCHAR2(50)
 CITY                                               VARCHAR2(30)
 STATE                                              CHAR(2)
 ZIP_CODE                                           VARCHAR2(12)
 PHONE_NUMBER                                       VARCHAR2(12)
 EMAIL_ADDRESS                                      VARCHAR2(50)

it is not showing the column as not null in table description but if I use 2nd statement now it gives me error message as the column is already having Not null.

ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

can any one please brief the difference between two statements.

Report message to a moderator

Re: Need few clarification on Alter table. [message #643721 is a reply to message #643720]

Fri, 16 October 2015 05:34

John Watson
Messages: 9003
Registered: January 2010
Location: Global Village

Senior Member

I get the same result (using release 12.1.0.2):

orclz>
orclz> desc dept
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 LOC                                                                  VARCHAR2(13)

orclz> alter table dept modify(dname not null disable);

Table altered.

orclz> desc dept
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 DEPTNO                                                      NOT NULL NUMBER(2)
 DNAME                                                                VARCHAR2(14)
 LOC                                                                  VARCHAR2(13)

orclz> alter table dept modify(dname not null);
alter table dept modify(dname not null)
                        *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL


orclz>

I would say that it is just an anomaly in the way the SQL*Plus DESCRIBE command happens to work: when it constructs its output, it ignores disables NOT NULL constraints. So to get guaranteed accurate results, one needs to query DBA_CONSTRAINTS.
Surprising.

Report message to a moderator

Re: Need few clarification on Alter table. [message #643723 is a reply to message #643721]

Fri, 16 October 2015 05:49

gopieee16
Messages: 28
Registered: May 2011
Location: Chennai

Junior Member

Thanks John,

for the clarification, can you please explain me the difference between the two statements


ALTER TABLE "RTMS_WEBDB"."APPOINTMENT_SURVEY" MODIFY ("DATE_ENTERED" NOT NULL DISABLE);
ALTER TABLE "RTMS_WEBDB"."APPOINTMENT_SURVEY" MODIFY ("DATE_ENTERED" NOT NULL);

Report message to a moderator

Re: Need few clarification on Alter table. [message #643724 is a reply to message #643723]

Fri, 16 October 2015 05:52

John Watson
Messages: 9003
Registered: January 2010
Location: Global Village

Senior Member

http://docs.oracle.com/database/121/ADMIN/general.htm#ADMIN11546

Report message to a moderator

Re: Need few clarification on Alter table. [message #643726 is a reply to message #643720]

Fri, 16 October 2015 06:32

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is 2 points regarding NOT NULL:
1/ Column property
2/ Column constraint

The former is showed in DESCRIBE command not the latter:

SQL> create table t (val int);

Table created.

SQL> set lines 65
SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                                       NUMBER(38)

SQL> alter table t modify (val not null disable);

Table altered.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                                       NUMBER(38)

SQL> select constraint_name, status, search_condition from user_constraints where table_name = 'T';
CONSTRAINT_N STATUS
------------ --------
SEARCH_CONDITION
----------------------------------------------------------------------------------------------------
SYS_C0051529 DISABLED
"VAL" IS NOT NULL

1 row selected.

SQL> alter table t modify (val not null);
alter table t modify (val not null)
                      *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

SQL> drop table t;

Table dropped.

SQL> create table t (val int)
  2  ;

Table created.

SQL> alter table t modify (val not null);

Table altered.

SQL> set lines 65
SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                              NOT NULL NUMBER(38)

SQL> select constraint_name, status, search_condition from user_constraints where table_name = 'T';
CONSTRAINT_N STATUS
------------ --------
SEARCH_CONDITION
----------------------------------------------------------------------------------------------------
SYS_C0051532 ENABLED
"VAL" IS NOT NULL

SQL> alter table t modify (val not null disable);
alter table t modify (val not null disable)
                      *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

The column property can't be disabled so in the first test you create a constraint, in the second you set the column property AND create the constraint.

This behavior and error were added in the most recent versions because in former ones when you copied the table several times (for instance using export/import) you multiplied the NOT NULL constraints.

Report message to a moderator

Re: Need few clarification on Alter table. [message #643734 is a reply to message #643726]

Fri, 16 October 2015 08:21

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And I didn't finish the demonstration from your case:

SQL> drop table t;

Table dropped.

SQL> create table t (val int);

Table created.

SQL> alter table t modify (val not null disable);

Table altered.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                                       NUMBER(38)

SQL> select constraint_name, status, search_condition from user_constraints where table_name = 'T';
CONSTRAINT_N STATUS   SEARCH_CONDITION
------------ -------- ----------------------------------------
SYS_C0051535 DISABLED "VAL" IS NOT NULL

1 row selected.

SQL>  alter table t enable constraint SYS_C0051535;

Table altered.

SQL> select constraint_name, status, search_condition from user_constraints where table_name = 'T';
CONSTRAINT_N STATUS   SEARCH_CONDITION
------------ -------- ----------------------------------------
SYS_C0051535 ENABLED  "VAL" IS NOT NULL

1 row selected.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                              NOT NULL NUMBER(38)

When you enable the constraint you set the column property.

Report message to a moderator

Re: Need few clarification on Alter table. [message #643805 is a reply to message #643734]

Mon, 19 October 2015 02:16

gopieee16
Messages: 28
Registered: May 2011
Location: Chennai

Junior Member

Thanks Michel, for the detailed explanation Smile

Report message to a moderator

Previous Topic:	in, out parameters
Next Topic:	user schema can't create or alter job (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 11 10:07:35 CDT 2026