Oracle Query

Question

Hi all, Constraint name contains table name or not. any one give me reply on this.

Vulpes · Answer

No, a constraint name can be any identifier you like and need not contain the table name.

Vulpes · Answer

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.

Vulpes · Answer

Try something like: SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND CONTAINS(CONSTRAINT_NAME, TABLE_NAME) > 0

Pulagara Murali · Answer

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.

Oracle Query

Answers (4)