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.
CodePudding user response：
To satisfy
FROM all_cons_columns ac
JOIN all_constraints cc
you should be joining on OWNER, CONSTRAINT_NAME. I'm not sure why you are using INDEX_NAME here.
CodePudding user response：
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:
- I was missing to join using
column_name(not sure why I missed that). WHEREclause was using a field from theactable (intermediate), and it should have beenat(the first one).
I hope this can help to anyone going through something similar.