Display constraints check SYS_

[Abderrahmane]

Hi,

I have question:
In PL/SQL Developer v10, when the View button is used to display the constraints check of a table, PL/SQL Developer does not display the check constraints of sys_ type, against if I make a sql query to display the same constraints, they appear.

Thanks,

Abder

 

[Marco Kalter]

Can you provide an example of such a constraint?

 

[Abderrahmane]

It is possible de send the screenshot with the forum.

select owner,constraint_name,
DECODE(constraint_type,'C','Check','P','Pimary Key','R','Foreign Key','U','Unique Key') Type_const,
SEARCH_CONDITION
from all_constraints
where table_name='xxx_table'
and owner='xxx';

This query display the constraints SYS_Cxxxx, but If you use the View button in the table (SQL Window) , it does not display sys_

 

[Marco Kalter]

What I meant is: can you let me know the condition of such a check constraint? For example: check(price >= 0)

 

[Abderrahmane]

Example: Statut is NOT NULL (constraint_name is SYS_C000000), constraint type = CHECK.

 

[Abderrahmane]

Create two constraints, one with a name and other unnamed (Oracle will assign the default name SYS_Cxxxx for unnamed constraint) Go to SQL Window and do a select on the table, then view (right mouse button), you will see it does not display the sys_ constraint, but if you use the query Command Window, it will display it sys_cxxxx.

 

[Abderrahmane]

Here is an example:
create table test (id number, status varchar2(10));
alter table test
add constraint id_pk primary key(id); (constraint with the name id_pk).
alter table test
add constraint status not null; (unnamed constraint - Oracle will assign the name SYS_Cxxxx)

 

[Marco Kalter]

Such an unnamed constraint will show up as an inline NOT NULL constraint:

create table TEST
(
  id     NUMBER not null,
  status VARCHAR2(10) not null
)

 

[Abderrahmane]

Look my example:
SQL> create table test (id number);
Table created

SQL> alter table test
2 modify id not null;
Table altered

I created the constraints NOT NULL unnamed. Oracle will assign the name SYS_C0011850

SQL> select * from all_constraints
where table_name='TEST';

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_COND
HR SYS_C0011850 C "ID" IS NOT NULL

In Command SQL, it is correct. PLSQL Developer display all constraints, but in object browser, I use right button in the table TEST, VIEW and the Check Constraints not displayed.

 

[Abderrahmane]

Hi Marco,

I do not have answers. IS there a bug in PL/SQL Developer ?
Thanks,

 

[Marco Kalter]

It's not a bug. As stated before, not null constraints are displayed inline.

 

[Abderrahmane]

Hi Marco,

All constraints defined by Oracle (SYS_Cxxxx) are not displayed in graphical mode (view table).
Following my example, you'll see.

 

[Abderrahmane]

Please send me your email to send screenshot to understand the situation.
Thanks.

 

[IvanZ]

In Command SQL, it is correct. PLSQL Developer display all constraints, but in object browser, I use right button in the table TEST, VIEW and the Check Constraints not displayed.

It is displayed as an empty checkbox in the Nullable column under Columns tab. Thats why it's not displayed under Checks tab.

 

[Abderrahmane]

No I'm talking about the constraints that are created by oracle SYS_Cxxxx. They are not displayed in graphical mode (view table).

 

[Abderrahmane]

The constraints NOT NULL not displayed in graphical mode (view table).

 

[Abderrahmane]

I will prepare a document with screenshot to understand the situation of constraints.

Thanks.