Oracle Query

Pulagara Murali

13y1.5kOracle

Hi all,

Constraint name contains table name or not.

any one give me reply on this.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted answer
No, a constraint name can be any identifier you like and need not contain the table name.
0
Vulpes13y ago
Sorry, I've given you a query there which gets foreign keys which do contain the table name.

To get a list of ones that don't try instead:

SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND CONSTRAINT_NAME NOT LIKE ('FK\_' || TABLE_NAME || '\_%') ESCAPE '\'

Note that I've had to escape the underscores here so that they're treated literally rather than matching any single character within the LIKE clause.
0
Vulpes13y ago
Try something like:

SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND CONTAINS(CONSTRAINT_NAME, TABLE_NAME) > 0
0
Pulagara Murali13y ago
Dear Vulpes,

Thanks for giving response. My actual query is, earlier in my company we have given Foreign key names without following naming conventions. But foreign key preceded with FK_somename. Now we following FK_TBNAMNE_RTB. I need a query on Foreign key which foreign key doesn't having table name.

We are following naming convention is :
FK_TBNAME_RTB

TBNAME : TABLE NAME
RTB : REFERENCE TABLE NAME

can you give me query for list of foreign keys which are not following above structure.

Thanks and Regards,
Murali P.
0