Home » SQL & PL/SQL » SQL & PL/SQL » finding constrains on table (oracle 12c)
| finding constrains on table [message #653572] |
Mon, 11 July 2016 14:11  |
 |
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
Hello experts,
In oracle when we try to update/delete on tables we get unique constraints, primary and foreign key errors. Is there a way to find unique, primary and foreign key constraints on given table using sql
Thanks
|
|
|
|
|
|
|
|
| Re: finding constrains on table [message #653585 is a reply to message #653584] |
Mon, 11 July 2016 15:49  |
|
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
Is below query is correct to look for primary/Referential and unique constraints.
SELECT distinct table_name
FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R' -- "Referential integrity"
AND r_constraint_name IN (
SELECT constraint_name
FROM ALL_CONSTRAINTS
WHERE table_name = '<table_name>'
AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
)[Updated on: Mon, 11 July 2016 15:49] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: finding constrains on table [message #653592 is a reply to message #653572] |
Mon, 11 July 2016 20:32 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the following simple example.
Suppose you have some tables like these:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE parent_tab
2 (cola NUMBER,
3 CONSTRAINT cola_pk PRIMARY KEY (cola))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE child_tab
2 (col1 NUMBER,
3 col2 NUMBER,
4 col3 NUMBER,
5 col4 NUMBER,
6 CONSTRAINT col1_pk PRIMARY KEY (col1),
7 CONSTRAINT col2_uk UNIQUE (col2),
8 CONSTRAINT col3_fk FOREIGN KEY (col3) REFERENCES parent_tab (cola),
9 CONSTRAINT col4 CHECK (col4 > 0))
10 /
Table created.
You can use a query like this to get some of the constraint information.
I used short names and narrow columns just to make it easier to display here.
You might need to increase the sizes for the formats and use word_wrapped.
SCOTT@orcl_12.1.0.2.0> COLUMN table_name FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN constraint FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN columns FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN condition FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN ref_tab FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN ref_cols FORMAT A10
SCOTT@orcl_12.1.0.2.0> SELECT uc.table_name,
2 uc.constraint_name constraint,
3 DECODE
4 (uc.constraint_type,
5 'P', 'primary',
6 'U', 'unique',
7 'R', 'foreign',
8 'C', 'check') type,
9 ucc.column_name columns,
10 uc2.table_name ref_tab,
11 ucc2.column_name ref_cols,
12 uc.search_condition condition
13 FROM user_constraints uc, user_cons_columns ucc,
14 user_constraints uc2, user_cons_columns ucc2
15 WHERE uc.constraint_name = ucc.constraint_name
16 AND uc.r_constraint_name = uc2.constraint_name(+)
17 AND uc2.constraint_name = ucc2.constraint_name(+)
18 AND uc.table_name in ('PARENT_TAB', 'CHILD_TAB')
19 ORDER BY table_name, constraint
20 /
TABLE_NAME CONSTRAINT TYPE COLUMNS REF_TAB REF_COLS CONDITION
---------- ---------- ------- ---------- ---------- ---------- ----------
CHILD_TAB COL1_PK primary COL1
CHILD_TAB COL2_UK unique COL2
CHILD_TAB COL3_FK foreign COL3 PARENT_TAB COLA
CHILD_TAB COL4 check COL4 col4 > 0
PARENT_TAB COLA_PK primary COLA
5 rows selected.
|
|
|
|
| Re: finding constrains on table [message #653595 is a reply to message #653592] |
Tue, 12 July 2016 00:55 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
It does not work if constraint is on several columns:
SQL> drop table parent_tab;
drop table parent_tab
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE parent_tab
2 (cola NUMBER, colb NUMBER,
3 CONSTRAINT cola_pk PRIMARY KEY (cola,colb))
4 /
Table created.
SQL> drop TABLE child_tab;
drop TABLE child_tab
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE child_tab
2 (col1 NUMBER,
3 col2 NUMBER,
4 col3 NUMBER,
5 col4 NUMBER,
6 CONSTRAINT col1_pk PRIMARY KEY (col1),
7 CONSTRAINT col2_uk UNIQUE (col2),
8 CONSTRAINT col3_fk FOREIGN KEY (col3,col4) REFERENCES parent_tab (cola,colb),
9 CONSTRAINT col4 CHECK (col4 > 0))
10 /
Table created.
SQL> COLUMN table_name FORMAT A10
SQL> COLUMN constraint FORMAT A10
SQL> COLUMN columns FORMAT A10
SQL> COLUMN condition FORMAT A10
SQL> COLUMN ref_tab FORMAT A10
SQL> COLUMN ref_cols FORMAT A10
SQL> SELECT uc.table_name,
2 uc.constraint_name constraint,
3 DECODE
4 (uc.constraint_type,
5 'P', 'primary',
6 'U', 'unique',
7 'R', 'foreign',
8 'C', 'check') type,
9 ucc.column_name columns,
10 uc2.table_name ref_tab,
11 ucc2.column_name ref_cols,
12 uc.search_condition condition
13 FROM user_constraints uc, user_cons_columns ucc,
14 user_constraints uc2, user_cons_columns ucc2
15 WHERE uc.constraint_name = ucc.constraint_name
16 AND uc.r_constraint_name = uc2.constraint_name(+)
17 AND uc2.constraint_name = ucc2.constraint_name(+)
18 AND uc.table_name in ('PARENT_TAB', 'CHILD_TAB')
19 ORDER BY table_name, constraint
20 /
TABLE_NAME CONSTRAINT TYPE COLUMNS REF_TAB REF_COLS CONDITION
---------- ---------- ------- ---------- ---------- ---------- ----------
CHILD_TAB COL1_PK primary COL1
CHILD_TAB COL2_UK unique COL2
CHILD_TAB COL3_FK foreign COL4 PARENT_TAB COLA
CHILD_TAB COL3_FK foreign COL3 PARENT_TAB COLB
CHILD_TAB COL3_FK foreign COL4 PARENT_TAB COLB
CHILD_TAB COL3_FK foreign COL3 PARENT_TAB COLA
CHILD_TAB COL4 check COL4 col4 > 0
PARENT_TAB COLA_PK primary COLB
PARENT_TAB COLA_PK primary COLA
9 rows selected.
SQL> with
2 cons_columns as (
3 select owner, constraint_name,
4 listagg(column_name,',') within group (order by position) columns
5 from user_cons_columns
6 group by owner, constraint_name
7 )
8 SELECT uc.table_name,
9 uc.constraint_name constraint,
10 DECODE
11 (uc.constraint_type,
12 'P', 'primary',
13 'U', 'unique',
14 'R', 'foreign',
15 'C', 'check') type,
16 ucc.columns,
17 uc2.table_name ref_tab,
18 ucc2.columns ref_cols,
19 uc.search_condition condition
20 FROM user_constraints uc, cons_columns ucc,
21 user_constraints uc2, cons_columns ucc2
22 WHERE uc.constraint_name = ucc.constraint_name
23 AND uc.r_constraint_name = uc2.constraint_name(+)
24 AND uc2.constraint_name = ucc2.constraint_name(+)
25 AND uc.table_name in ('PARENT_TAB', 'CHILD_TAB')
26 ORDER BY table_name, constraint
27 /
TABLE_NAME CONSTRAINT TYPE COLUMNS REF_TAB REF_COLS CONDITION
---------- ---------- ------- ---------- ---------- ---------- ----------
CHILD_TAB COL1_PK primary COL1
CHILD_TAB COL2_UK unique COL2
CHILD_TAB COL3_FK foreign COL3,COL4 PARENT_TAB COLA,COLB
CHILD_TAB COL4 check COL4 col4 > 0
PARENT_TAB COLA_PK primary COLA,COLB
5 rows selected. |
|
|
|
|
|
| Re: finding constrains on table [message #653598 is a reply to message #653597] |
Tue, 12 July 2016 01:31 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Yes the result is incorrect, what is the meaning of these lines:
CHILD_TAB COL3_FK foreign COL4 PARENT_TAB COLA
CHILD_TAB COL3_FK foreign COL3 PARENT_TAB COLB
CHILD_TAB COL3_FK foreign COL4 PARENT_TAB COLB
CHILD_TAB COL3_FK foreign COL3 PARENT_TAB COLA
There 4 lines for 2-columns constraints.
Does CHILD_TAB.COL4 reference PARENT_TAB.COLA?
[Updated on: Tue, 12 July 2016 01:32] Report message to a moderator |
|
|
|
|
|
| Re: finding constrains on table [message #653601 is a reply to message #653600] |
Tue, 12 July 2016 01:58 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You're right. I see what you mean now. I added the missing outer join in the corrected code below.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE parent_tab
2 (cola NUMBER,
3 colb NUMBER,
4 CONSTRAINT cola_pk PRIMARY KEY (cola,colb))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE child_tab
2 (col1 NUMBER,
3 col2 NUMBER,
4 col3 NUMBER,
5 col4 NUMBER,
6 CONSTRAINT col1_pk PRIMARY KEY (col1),
7 CONSTRAINT col2_uk UNIQUE (col2),
8 CONSTRAINT col3_fk FOREIGN KEY (col3,col4) REFERENCES parent_tab (cola,colb),
9 CONSTRAINT col4 CHECK (col4 > 0))
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> COLUMN table_name FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN constraint FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN columns FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN condition FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN ref_tab FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN ref_cols FORMAT A10
SCOTT@orcl_12.1.0.2.0> SELECT uc.table_name,
2 uc.constraint_name constraint,
3 DECODE
4 (uc.constraint_type,
5 'P', 'primary',
6 'U', 'unique',
7 'R', 'foreign',
8 'C', 'check') type,
9 ucc.column_name columns,
10 uc2.table_name ref_tab,
11 ucc2.column_name ref_cols,
12 uc.search_condition condition
13 FROM user_constraints uc, user_cons_columns ucc,
14 user_constraints uc2, user_cons_columns ucc2
15 WHERE uc.constraint_name = ucc.constraint_name
16 AND uc.r_constraint_name = uc2.constraint_name(+)
17 AND uc2.constraint_name = ucc2.constraint_name(+)
18 AND uc.table_name in ('PARENT_TAB', 'CHILD_TAB')
19 AND ucc.position = ucc2.position(+)
20 ORDER BY table_name, constraint
21 /
TABLE_NAME CONSTRAINT TYPE COLUMNS REF_TAB REF_COLS CONDITION
---------- ---------- ------- ---------- ---------- ---------- ----------
CHILD_TAB COL1_PK primary COL1
CHILD_TAB COL2_UK unique COL2
CHILD_TAB COL3_FK foreign COL3 PARENT_TAB COLA
CHILD_TAB COL3_FK foreign COL4 PARENT_TAB COLB
CHILD_TAB COL4 check COL4 col4 > 0
PARENT_TAB COLA_PK primary COLA
PARENT_TAB COLA_PK primary COLB
7 rows selected.
|
|
|
|
| Re: finding constrains on table [message #653602 is a reply to message #653601] |
Tue, 12 July 2016 02:11 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
This is new in 12c! A table can now be outer joined to more than one table! This was not allowed in previous versions:
SQL> SELECT uc.table_name,
2 uc.constraint_name constraint,
3 DECODE
4 (uc.constraint_type,
5 'P', 'primary',
6 'U', 'unique',
7 'R', 'foreign',
8 'C', 'check') type,
9 ucc.column_name columns,
10 uc2.table_name ref_tab,
11 ucc2.column_name ref_cols,
12 uc.search_condition condition
13 FROM user_constraints uc, user_cons_columns ucc,
14 user_constraints uc2, user_cons_columns ucc2
15 WHERE uc.constraint_name = ucc.constraint_name
16 AND uc.r_constraint_name = uc2.constraint_name(+)
17 AND uc2.constraint_name = ucc2.constraint_name(+)
18 AND uc.table_name in ('PARENT_TAB', 'CHILD_TAB')
19 AND ucc.position = ucc2.position(+)
20 ORDER BY table_name, constraint
21 /
AND uc2.constraint_name = ucc2.constraint_name(+)
*
ERROR at line 17:
ORA-01417: a table may be outer joined to at most one other table
SQL> @v
Version Oracle : 11.2.0.4.0
I'd also add to your query ucc.position to the ORDER BY clause (and maybe a BREAK statement, or something showing a line is the continuation of the previous constraint, for a pretty display).
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 17:20:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
