Rebuild foreign Key

[former_SQLA_member1694872]

Hello, How do I save the foreign keys and to be re-created after changing one of the fields that make up the primary key?

[VolkerBarth]

In addition to Volker's suggestion, you can also use the sa_get_table_definition system function - this function returns a CREATE TABLE statement for the according table with index creation, trigger creation and privileges - as this also includes according ALTER TABLE ... ADD FOREIGN KEY clauses.

[former_SQLA_member1694872]

Hello, I was thinking more in that direction, get all the Foreign Keys and their composition and save them on a table and then rebuild.

I did so:

 CREATE TABLE TBFKCOMMAND(
 ID INTEGER IDENTITY,
 COMMAND_LINE TEXT,
 PRIMARY KEY (ID)
);

INSERT INTO TBFKCOMMAND (COMMAND_LINE)
select 'ALTER TABLE '||FK_TABLE_NAME||' ADD FOREIGN KEY '||FK_NAME||' ('||list(pk_column_name)||') REFERENCES '||PK_TABLE_NAME||' ('||list(fk_column_name)||');' AS COMANDO
from sa_oledb_foreign_keys()
where pk_table_name = 'table_pk'
and fk_table_name='table_fk'
and fk_name = 'fkName'
group by pk_table_name, fk_table_name, FK_NAME

[former_SQLA_member1694885]

If you're interested in a single foreign key or all foreign keys referencing a single table, navigate to the referenced table in SQL Central, change to tab "Referencing Constraints", select the desired entry or entries, copy them to the clipboard and paste them into a text editor. You can then delete them in the same view, adjust your PK definition, modify the ALTER TABLE statements in the text editor if required and copy them back (via clipboard) into a DBiSQL session to run them.
Otherwise, or if you don't have access to SQL Central, the easiest approach I'm aware of is to run the dbunload utility with the structure only cmd line option and filter the foreign key declarations out of the generated reload.SQL file.

HTH

Volker