Oracle Database Discussions

Hi all,

I created a table MANT (here is the script) :

CREATE TABLE DMI.MANT
(
  MANT     NUMBER(7)                            NOT NULL,
  SPER     NUMBER(7)                            NOT NULL,
  RANT     VARCHAR2(10 BYTE)                    NOT NULL,
  ANT      VARCHAR2(200 BYTE),
  CMT      VARCHAR2(800 BYTE),
  PERIODE  VARCHAR2(200 BYTE),
  DU       DATE,
  USR      VARCHAR2(10 BYTE),
  DTE      DATE
)

Then I added a column called RSMF :

ALTER TABLE DMI.MANT
ADD (RSMF VARCHAR2(100 BYTE));

and added a check constraint :

ALTER TABLE DMI.MANT ADD 
CONSTRAINT RSMF_NOT_NULL
 CHECK (RSMF IS NOT NULL)
 ENABLE
 NOVALIDATE

I had to add NOVALIDATE clause because all the records had a null value. So to validate the constraint I simply executed the following update

update dmi.mant set rsmf = 'yy';

and then validate the contraint with :

ALTER TABLE DMI.MANT
MODIFY CONSTRAINT RSMF_NOT_NULL
VALIDATE;

The problem comes when I query ALL_TAB_COLUMNS table...

Select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE
from all_tab_columns
where table_name = 'MANT'
   and owner = 'DMI'
   and COLUMN_NAME = 'RSMF';

Why does the ALL_TAB_COLUMNS table gives for RSMF column a NULLABLE value equals to 'Y' while there is a validated check NOT NULL constraint on it ?
It's annoying because I want to get all the NOT NULL columns in a given table and here the SRMF column is not returned...

Here is the result :

OWNER  |TABLE_NAME |COLUMN_NAME  |DATA_TYPE  |DATA_LENGTH  |NULLABLE
------------------------------------------------------------------------
DMI    |MANT       |RSMF         |VARCHAR2   |100          |Y

Thks for your replies...