Thread: Postgresql FK to MS SQL triggers

Postgresql FK to MS SQL triggers

From
"Igor Kryltsov"
Date:
Hi,


If you are moving from Postgres to MS SQL you will most likely will find
that you can not recreate your PostgreSQL FK to MSSQL FK because this
enterprise class database will NOT allow you to create all 3 FK which are
exist in your PGSQL:

table users(user_id PK)

table journal(created_by, modified_by, deleted_by)

ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES
users(user_id) MATCH FULL ON UPDATECASCADE ON DELETE CASCADE;
ADD CONSTRAINT fk_modified_by FOREIGN KEY (modified_by) REFERENCES
users(user_id) MATCH FULL ON UPDATECASCADE ON DELETE CASCADE;
ADD CONSTRAINT fk_deleted_by FOREIGN KEY (deleted_by) REFERENCES
users(user_id) MATCH FULL ON UPDATECASCADE ON DELETE CASCADE;


For interested people I wrote a PHP script which:
1) Extracts all underlying triggers from pg_trigger table  in Postgres used
to support FK (3 triggers for each FK)
2) Generates a MSSQL script file which recreates all triggers in MSSQL

Hope it will save some time for somebody.

Igor





Re: Postgresql FK to MS SQL triggers

From
Josh Berkus
Date:
Igor,

> For interested people I wrote a PHP script which:
> 1) Extracts all underlying triggers from pg_trigger table  in Postgres used
> to support FK (3 triggers for each FK)
> 2) Generates a MSSQL script file which recreates all triggers in MSSQL

How about a script which goes the other way?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco