I am trying to create foreign keys on a lot of tables at once using a script to generate all of the alter statements. Don't really feel like type all statements manually.
TABLE - PARENT TABLE NAME
<TABLE>_X_Y_Z - child table name format (<TABLE> means parent table name)
Here is a sample of how it should look:
ALTER TABLE SCHEMA.TABLE_X_Y_Z
ADD CONSTRAINT FK_TABLE_X_Y_Z FOREIGN KEY
So far I have this….
select 'alter table '||rtrim(tabschema)|| '.' ||rtrim(c.tabname)|| '
ADD CONSTRAINT FK_'||rtrim(c.tabname)|| ' FOREIGN KEY
..and then I thought maybe I don’t need to join to select child table name and parent table name because: all child table names are this format: <PARENT_TABLE_NAME>_XXXXX i.e. parent name is <PARENT> and child table name is <<PARENT>_X_Y_Z> (as u can see the string before the first _ is the same name as parent table—this will always be the case for all tables. How do I select the name <PARENT> from child table name? This way i can use whatever function possible to select the parent table name to refer to as well as the column name TABLE_HEX.