Foreign Key Constraints

dba_udb · #1 (**permalink**) 07-07-06, 13:47

OS: AIX,
UDB : 8.1 FP7

How do i get all the foreign key state ments to and for a table?

Here is what i am trying to do . I am dropping a table and recreating by modifying the order of the columns . But i see a lot of foreign key statemtns
fromother table to this table. Do they get effected when i drop the tables.

How do i take care of it.

thanks

sharrisdb2 · #2 (**permalink**) 07-07-06, 17:33

Yes, if you drop a table the associated RI drops too. You have to preserve the RI in DDL to be run either before or after you re-load the data in the new table (your choice).

An easy and cheap way to extract RI DDL is to use the IBM DB2 Command Center and right click on the table in question - you'll see "generate DDL" - do this and you will get the RI DDL you need. You may need to get the ddl from the other associated tables. To get this info - right click on the table - then click "show related" and look at the table tab.

You can use the db2look command too. Do some research on that one. You can also buy a tool like Quest Central for thousands of dollars and it do 99.9% of the work for you - including generating the entire export, drop table, build, import, and runstats, etc.

The manual way is to find all associated RI to the table and write your own DDL.

Hope this helps,

-- Steve
Certified DB2 LUW DBA

Peter.Vanroose · #3 (**permalink**) 07-07-06, 18:50

Of course you can also find out all dependencies (RI), indexes, views, check constraints, authorizations etc. on your table by using plain SQL instead of using the Control Center.

E.g., to find all indexes defined on your table:

Code:

SELECT 'CREATE ' || CASE uniquerule WHEN 'D' THEN '' ELSE 'UNIQUE' END ||
       'INDEX ' || name || ' ON ' || tbcreator || '.' || tbname
FROM sysibm.sysindexes WHERE tbname = 'YOURTABLE' AND tbcreator = 'TABLEOWNER'

(This is not at all complete: you will have to consult SYSIBM.SYSINDEXCOLUSE for the columns involved, you must collect the authorizations in SYSIBM.SYSINDEXAUTH, you must make sure to use the same bufferpool, ...)

Finding out about referential constraints in other tables is similar: query SYSIBM.SYSRELS (one line per foreign key) and SYSIBM.SYSKEYCOLUSE (one line per column of each FK). Don't forget to add the appropriate REFERENCES authorizations back (see SYSIBM.SYSTABAUTH) ...

Also make sure to place the table in the same tablespace as the old one.

Internet Services

Web Development

Digital Marketing

Consumer Tech