Compostie primary key of a composite primary key

Hello! 👋👋 I have a table Accounts with id as primary key


export const accounts = pgTable('accounts', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
});

Then I have the table sections where the primary key is a composite key of the table id and the account.id referenced key


export const sections = pgTable('sections', {
  id: text('id').notNull().$defaultFn(() => generateIdFromEntropySize(5)),
  account: uuid('account').notNull().references(() => accounts.id, {onUpdate: 'cascade', onDelete:'cascade'}),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.id, table.account] })
  }
})

The problem is that now i need a third table Events where the primary key is a composite between the table primary key (id) and the sections composite primary key:


export const events = pgTable('events', {
  id: text('id').notNull().$defaultFn(() => generateIdFromEntropySize(5)),
  section_id: text('section_id').notNull(),
  section_account: uuid('section_account').notNull(),
}, (table) => {
  return {
    sectionReference: foreignKey({
      columns: [table.section_id, table.section_account],
      foreignColumns: [sections.id, sections.account]
    }),
    pk: primaryKey({ columns: [table.id, table.section_id, table.section_account] })
  }
})

Everything is working fine but I don't know if it's the correct solution as I'm seeing duplicated named fields on neon after applying the migration.

2 Replies

rphlmr ⚡•2mo ago

👋 looks good to me!

CREATE TABLE IF NOT EXISTS "accounts" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL
);

CREATE TABLE IF NOT EXISTS "events" (
    "id" text NOT NULL,
    "section_id" text NOT NULL,
    "section_account" uuid NOT NULL,
    CONSTRAINT "events_id_section_id_section_account_pk" PRIMARY KEY("id","section_id","section_account")
);

CREATE TABLE IF NOT EXISTS "sections" (
    "id" text NOT NULL,
    "account" uuid NOT NULL,
    CONSTRAINT "sections_id_account_pk" PRIMARY KEY("id","account")
);

DO $$ BEGIN
 ALTER TABLE "events" ADD CONSTRAINT "events_section_id_section_account_sections_id_account_fk" FOREIGN KEY ("section_id","section_account") REFERENCES "public"."sections"("id","account") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "sections" ADD CONSTRAINT "sections_account_accounts_id_fk" FOREIGN KEY ("account") REFERENCES "public"."accounts"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "accounts" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL
);

CREATE TABLE IF NOT EXISTS "events" (
    "id" text NOT NULL,
    "section_id" text NOT NULL,
    "section_account" uuid NOT NULL,
    CONSTRAINT "events_id_section_id_section_account_pk" PRIMARY KEY("id","section_id","section_account")
);

CREATE TABLE IF NOT EXISTS "sections" (
    "id" text NOT NULL,
    "account" uuid NOT NULL,
    CONSTRAINT "sections_id_account_pk" PRIMARY KEY("id","account")
);

DO $$ BEGIN
 ALTER TABLE "events" ADD CONSTRAINT "events_section_id_section_account_sections_id_account_fk" FOREIGN KEY ("section_id","section_account") REFERENCES "public"."sections"("id","account") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "sections" ADD CONSTRAINT "sections_account_accounts_id_fk" FOREIGN KEY ("account") REFERENCES "public"."accounts"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

https://drizzle.run/zyjuua9h3uijqs2naa4iz9wf

Drizzle Run

Composite primary and foreign keys - Drizzle Run

marcbejar•2mo ago

Thank you very much for the answer. I will keep coding like so. Thanks a lot!

Gaming

Programming

Compostie primary key of a composite primary key