BEGIN WORK;

-- Drop foreign keys to Relaties.

ALTER TABLE Boekstukregels
DROP CONSTRAINT "boekstukregels_bsr_rel_code_fkey";

ALTER TABLE Journal
DROP CONSTRAINT "journal_jnl_rel_fkey";

-- Drop primary key Relaties.

ALTER TABLE Relaties
DROP CONSTRAINT "relaties_pkey";

-- New PK constraint for Relaties.
ALTER TABLE Relaties
ADD CONSTRAINT "relaties_pkey" PRIMARY KEY (rel_code, rel_ledger);

-- Add column bsr_dbk_id for Boekstukregels.

ALTER TABLE Boekstukregels
ADD COLUMN bsr_dbk_id VARCHAR(4) REFERENCES Dagboeken;

-- Fix FK to Relaties.

ALTER TABLE Boekstukregels
ADD CONSTRAINT "bsr_fk_rel"
FOREIGN KEY (bsr_rel_code, bsr_dbk_id) REFERENCES Relaties;

-- Fill.
UPDATE Boekstukregels
SET bsr_dbk_id =
  ( SELECT rel_ledger FROM Relaties WHERE rel_code = Boekstukregels.bsr_rel_code );

-- Add column jnl_rel_dbk_id for Journal.

ALTER TABLE Journal
ADD COLUMN jnl_rel_dbk VARCHAR(4) REFERENCES Dagboeken;

-- Fix FK to Relaties.
ALTER TABLE Journal
ADD CONSTRAINT "jnl_fk_rel"
FOREIGN KEY (jnl_rel, jnl_rel_dbk) REFERENCES Relaties;

-- Fill.
UPDATE Journal
SET jnl_rel_dbk =
  ( SELECT rel_ledger FROM Relaties WHERE rel_code = Journal.jnl_rel_dbk);

-- Bump version.

UPDATE Constants
  SET value = '11'
  WHERE name = 'SCM_REVISION' AND value = '10';
UPDATE Metadata
  SET adm_scm_revision =
    (SELECT int2(value) FROM Constants WHERE name = 'SCM_REVISION');

COMMIT WORK;