oracleconstraintsdata-dictionary

Is the OWNER in ALL_CONSTRAINTS and ALL_CONS_COLUMNS the same as the table owner?

According to Oracle, the OWNER field in (ALL|USER|DBA)_CONSTRAINTS is the owner of the constraint. To relate back to the table that this constraint belongs to, this view provides TABLE_NAME, however to uniquely identify a table, I need both the table's owner and the table name. Because the *_CONSTRAINTS and *_ALL_CONS_COLUMNS views do not have a TABLE_OWNER field, does this mean that the constraint owner is the same as the table owner? Or in other words, can a constraint only be added by the owner of the table?

Solution

  • does this mean that the constraint owner is the same as the table owner?

    Yes.

    can a constraint only be added by the owner of the table?

    No, it can be added by another user that has alter any table privileges. One of such is SYS. Here's an example:

    Connected as Scott, I'm creating a table:

    SQL> connect scott/tiger
Connected.
SQL> create table test (id number);

Table created.

    Connect as SYS and alter Scott's table - add primary key constraint:

    SQL> connect sys as sysdba

Enter password:
Connected.
SQL> alter table scott.test add constraint pk_test primary key (id);

Table altered.

    Back to Scott, to check who owns what:

    SQL> select constraint_name, owner from all_constraints where table_name = 'TEST';

CONSTRAINT_NAME                OWNER
------------------------------ ------------------------------
PK_TEST                        SCOTT

SQL> select table_name, owner From all_tables where table_name = 'TEST';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
TEST                           SCOTT

SQL>

    As you can see, Scott owns everything.