I am trying to view the constraints for all the tables owned by me using the dictionary USER_CONTRAINTS. This gives me 67 records (all constraints on all my tables combined)
Post that I want to see the columns on which these constraints apply. So when I do a select* operation on the dictionary USER_CONS_COLUMNS, it returns me 69 records.
My question is how can the number of records be different? Ultimately, both the dictionary views only fetch those records that are relevant to me(as user).
This is leading to incorrect number of records being returned when I perform a join on both these tables.
Also, if the above behaviour is expected, when which of the two should I be choosing as master table/view while JOINing?
My question is how can the number of records be different ?
Easily. Composite foreign keys, for example.
SQL> create table test_m
2 (id number,
3 id_area number,
4 constraint pk_m primary key (id, id_area)
5 );
Table created.
SQL> create table test_d
2 (id number,
3 idm number,
4 idm_area number,
5 constraint fk_dm foreign key (idm, idm_area)
6 references test_m (id, id_area)
7 );
Table created.
SQL> select table_name, constraint_name, constraint_type
2 from user_constraints where table_name in ('TEST_M', 'TEST_D')
3 order by table_name;
TABLE_NAME CONSTRAINT_NAME C
------------------------------ --------------- -
TEST_D FK_DM R
TEST_M PK_M P
SQL> select table_name, column_name, constraint_name
2 from user_cons_columns where table_name in ('TEST_M', 'TEST_D')
3 order by table_name;
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
------------------------------ --------------- ---------------
TEST_D IDM_AREA FK_DM
TEST_D IDM FK_DM
TEST_M ID_AREA PK_M
TEST_M ID PK_M
SQL>
This is leading to incorrect number of records being returned when I perform a join on both these tables.
I'd say that you're not doing it correctly. However, as it is difficult to debug code you can't see, I can't tell much more.