Re: DB migration : Sybase to Postgres

On 2023-05-25 08:10:42 -0500, Ron wrote:
> (You can create the FKs ahead of time, but use the NOT VALID clause; then,
> after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.)

I don't think this will work:

| Normally, [ADD CONSTRAINT] will cause a scan of the table to verify
| that all existing rows in the table satisfy the new constraint. But if
| the NOT VALID option is used, this potentially-lengthy scan is
| skipped. The constraint will still be enforced against subsequent
| inserts or updates
(https://www.postgresql.org/docs/current/sql-altertable.html)

However, you can define a constraint as DEFERRABLE, and then defer
checking until commit:

hjp=> create table master(id int primary key, t text);
CREATE TABLE
hjp=> create table detail(id int primary key, master int references master DEFERRABLE INITIALLY IMMEDIATE, t text);
CREATE TABLE

hjp=> begin;
BEGIN
hjp=*> set constraints detail_master_fkey deferred;
SET CONSTRAINTS
hjp=*> insert into detail values(1, 1, '1/1');
INSERT 0 1
hjp=*> insert into detail values(2, 1, '1/2');
INSERT 0 1
hjp=*> insert into detail values(3, 2, '2/1');
INSERT 0 1
hjp=*> insert into detail values(4, 3, '3/1');
INSERT 0 1
hjp=*> insert into master values(1, '1');
INSERT 0 1
hjp=*> insert into master values(2, '2');
INSERT 0 1

-- We haven't inserted a master record with id 3 yet, so the commit will
-- fail:

hjp=*> commit;
ERROR: insert or update on table "detail" violates foreign key constraint "detail_master_fkey"
DETAIL: Key (master)=(3) is not present in table "master".

(You can also reenable the constraint explicitely before the end of a
transaction with SET CONSTRAINTS ... IMMEDIATE)

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"