DT
Drizzle Team•2w ago
Mozart's_Ghost

composite primary key migration fail

I am trying to add a composite primary key via a custom migration file. I ran drizzle-kit --custom --name=recipient-primary-key to generate a custom migration file. In that file i have the following sql 
-- Step 1: Rename the old recipients table
ALTER TABLE recipients RENAME TO recipients_old;

-- Step 2: Create the new recipients table with a composite primary key
CREATE TABLE recipients (
    subscription_id TEXT NOT NULL,
    contact_id TEXT NOT NULL,
    preferred_time TEXT NOT NULL,
    PRIMARY KEY (subscription_id, contact_id),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON UPDATE NO ACTION ON DELETE NO ACTION
);

-- Step 3: Copy valid data from recipients_old to the new recipients table
INSERT INTO recipients (subscription_id, contact_id, preferred_time)
SELECT subscription_id, contact_id, preferred_time FROM recipients_old
WHERE subscription_id IS NOT NULL AND contact_id IS NOT NULL;

-- Step 4: Drop the old table
DROP TABLE recipients_old;
-- Step 1: Rename the old recipients table
ALTER TABLE recipients RENAME TO recipients_old;

-- Step 2: Create the new recipients table with a composite primary key
CREATE TABLE recipients (
    subscription_id TEXT NOT NULL,
    contact_id TEXT NOT NULL,
    preferred_time TEXT NOT NULL,
    PRIMARY KEY (subscription_id, contact_id),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON UPDATE NO ACTION ON DELETE NO ACTION
);

-- Step 3: Copy valid data from recipients_old to the new recipients table
INSERT INTO recipients (subscription_id, contact_id, preferred_time)
SELECT subscription_id, contact_id, preferred_time FROM recipients_old
WHERE subscription_id IS NOT NULL AND contact_id IS NOT NULL;

-- Step 4: Drop the old table
DROP TABLE recipients_old;
When I run the migration with drizzle-kit migrate and then inspect my local sqlite binary I only see a recipients_old table. It seems only the first statement in the migration file is successful. When I run these commands manually using sqlite3 they work. Not sure how I can go about debugging.
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?