Re: foreign keys

To find foreign keys that belong to a table, ie that are fired when the table row is modified, use:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('&1') AND CONSTRAINT_TYPE = 'R'; In SQL*PLUS, enter the table name at the prompt.

To find all constraints AGAINST a particular table, that is where the table in question is the parent, you need something like this:

	SELECT dc1.Constraint_Name, 
		dc1.Table_Name Child,
		dc1.r_Constraint_Name, 
		dc2.Table_Name Parent 
	FROM dba_constraints dc1, dba_constraints dc2 
	WHERE dc1.r_Constraint_Name =	dc2.Constraint_Name
		AND dc1.owner = UPPER('&1')
		AND dc2.owner = UPPER('&1')

Again, enter the table name at the prompt. I have not run this lately, so you might need to tweak it. I clipped it out of a "regen" utility I created.

Roger Snowden

> What select statement would I use to see if there are any foreign
> keys on a table.
Received on Wed Mar 04 1998 - 00:00:00 CST