[FIXED] How to join all_tab_columns + all_cons_columns + all_constraints in Oracle

Issue

Creating this table:

CREATE SEQUENCE my_seq;
CREATE TABLE mytable (
    id NUMBER(10) DEFAULT my_seq.nextval PRIMARY KEY,
    name VARCHAR2(10) NOT NULL,
    version FLOAT, 
    active CHAR, 
    updated DATE CONSTRAINT date_uk UNIQUE
)

I'm trying to join all_tab_columns, all_cons_columns and all_constraints tables (which are Oracle system tables, with information columns, etc).

If I join the first two:

SELECT at.owner, at.column_name, ac.constraint_name
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac 
  ON (at.table_name = ac.table_name 
    AND at.owner = ac.owner 
    AND at.column_name = ac.column_name)
WHERE at.table_name = 'MYTABLE';

I get:

OWNER           COLUMN_NAME     CONSTRAINT_NAME
--------------- --------------- ---------------
TEST            ID              SYS_C008423
TEST            NAME            SYS_C008422
TEST            VERSION
TEST            ACTIVE
TEST            UPDATED         DATE_UK

When I join the later ones (all_cons_columns and all_constraints):

SELECT ac.owner, ac.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_cons_columns ac
JOIN all_constraints cc 
  ON (ac.constraint_name = cc.constraint_name)
WHERE ac.table_name = 'MYTABLE';

I get:

OWNER           COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- --------------- ---------------
TEST            NAME            SYS_C008422     C               GENERATED NAME
TEST            ID              SYS_C008423     P               GENERATED NAME
TEST            UPDATED         DATE_UK         U               USER NAME

So far, so good.

But, when I try to join the 3 tables:

SELECT at.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac 
  ON (at.table_name = ac.table_name 
    AND at.owner = ac.owner)
LEFT JOIN all_constraints cc 
  ON (ac.constraint_name = cc.constraint_name 
    AND ac.owner = cc.owner 
    AND ac.table_name = cc.table_name)
WHERE ac.table_name = 'MYTABLE';

I get this:

COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- ---------------
ID              SYS_C008422     C               GENERATED NAME
NAME            SYS_C008422     C               GENERATED NAME
VERSION         SYS_C008422     C               GENERATED NAME
ACTIVE          SYS_C008422     C               GENERATED NAME
UPDATED         SYS_C008422     C               GENERATED NAME
ID              SYS_C008423     P               GENERATED NAME
NAME            SYS_C008423     P               GENERATED NAME
VERSION         SYS_C008423     P               GENERATED NAME
ACTIVE          SYS_C008423     P               GENERATED NAME
UPDATED         SYS_C008423     P               GENERATED NAME
ID              DATE_UK         U               USER NAME

COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- ---------------
NAME            DATE_UK         U               USER NAME
VERSION         DATE_UK         U               USER NAME
ACTIVE          DATE_UK         U               USER NAME
UPDATED         DATE_UK         U               USER NAME

I'm expecting this:

COLUMN_NAM CONSTRAINT  CONSTRAINT_TYPE GENERATED
---------- ----------- --------------- --------------
ID         SYS_C008423 P               GENERATED NAME
NAME       SYS_C008422 C               USER NAME
VERSION    
ACTIVE     
UPDATED    DATE_UK     U               USER NAME

Where is the mistake?

Using Oracle 12 XE

HOW TO TEST If you have docker:

docker run --name oracle_test \
  -e "ORACLE_PASSWORD=test" \
  -e "APP_USER=test" \
  -e "APP_USER_PASSWORD=test" \
  -p 1521:1521 \
  -d gvenzl/oracle-xe:21-slim

Then wait few minutes, and connect:

docker exec -it oracle_test sqlplus -l test/test@localhost:1521/XEPDB1

... and execute the CREATE SEQUENCE and CREATE TABLE statements.

Reply to Connor McDonald

If I change the SQL to:

SELECT at.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac ON (at.table_name = ac.table_name AND at.owner = ac.owner)
JOIN all_constraints cc ON (ac.constraint_name = cc.constraint_name AND ac.owner = cc.owner AND ac.table_name = cc.table_name)
WHERE ac.table_name = 'MYTABLE';

(Using JOIN on cc instead of LEFT JOIN):

Result:

COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- ---------------
ID              SYS_C008422     C               GENERATED NAME
NAME            SYS_C008422     C               GENERATED NAME
VERSION         SYS_C008422     C               GENERATED NAME
ACTIVE          SYS_C008422     C               GENERATED NAME
UPDATED         SYS_C008422     C               GENERATED NAME
ID              SYS_C008423     P               GENERATED NAME
NAME            SYS_C008423     P               GENERATED NAME
VERSION         SYS_C008423     P               GENERATED NAME
ACTIVE          SYS_C008423     P               GENERATED NAME
UPDATED         SYS_C008423     P               GENERATED NAME
ID              DATE_UK         U               USER NAME

COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- ---------------
NAME            DATE_UK         U               USER NAME
VERSION         DATE_UK         U               USER NAME
ACTIVE          DATE_UK         U               USER NAME
UPDATED         DATE_UK         U               USER NAME

Not what I'm expecting. I get the same result even if I change both LEFT JOIN to JOIN.

Solution

Fixed my issue:

SELECT at.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac 
  ON (at.owner = ac.owner
    AND at.table_name = ac.table_name 
    AND at.column_name = ac.column_name)
LEFT JOIN all_constraints cc 
  ON (ac.constraint_name = cc.constraint_name)    
WHERE at.table_name = 'MYTABLE';

I get:

COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- ---------------
NAME            SYS_C008422     C               GENERATED NAME
ID              SYS_C008423     P               GENERATED NAME
UPDATED         DATE_UK         U               USER NAME
VERSION
ACTIVE

I had two issues:

1. I was missing to join using column_name (not sure why I missed that).
2. WHERE clause was using a field from the ac table (intermediate), and it should have been at (the first one).

I hope this can help to anyone going through something similar.

Answered By - lepe

Answer Checked By - David Goodson (PHPFixing Volunteer)