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?

asked 31 May '19, 15:00

Walmir%20Taques's gravatar image

Walmir Taques
690374151
accept rate: 12%


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.

permanent link

answered 03 Jun '19, 03:02

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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

permanent link

answered 01 Jun '19, 12:55

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
5453715
accept rate: 25%

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
permanent link

answered 03 Jun '19, 09:02

Walmir%20Taques's gravatar image

Walmir Taques
690374151
accept rate: 12%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31

question asked: 31 May '19, 15:00

question was seen: 1,437 times

last updated: 03 Jun '19, 09:02