Re: Constraint oddity

Niall Litchfield wrote:

>I wonder if anyone has seen this before
>1 select constraint_name,table_name from all_constraints
>2* where constraint_type='?'
>SQL> /
>
>CONSTRAINT_NAME TABLE_NAME
>------------------------------ ------------------------------
>SYS_C001088 AQ$_REPLAY_INFO
>
>We don't use AQ. its a 9206 SE database.
>
>
>

Niall, that is a NOT NULL constraint SYS_C001091 on the table AQ$_REPLAY_INFO
and the condition is "AGENT" IS NOT NULL. It is, essentially, a constraint of the type "M"
(Dial "M" for constraints, or "MYSTERIOUS") because of the type of the agent column.
The AGENT column type is AQ$_AGENT. I believe that Oracle is not able to discern the
constraint type if the underlying column is of an object type. To prove my assumption, I did the following:

SQL> create table a(a sys.aq$_agent not null);

Table created.

SQL> select constraint_name, constraint_type from user_constraints   2 where table_name='A';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C002314                    ?

SQL> So, I created my very own constraint of the type '?'. If I may suggest to Oracle choosing a different type, like 'F' (for Foobar or any other F-word) or something on that line. Even 'S' will do. Don't worry, I'm not
going to start explaining that one.

-- 
Mladen Gogala
Oracle DBA
Ext. 121


--
http://www.freelists.org/webpage/oracle-l