How to create Composite Primary Key?

Feature Request
#1

Hi Wappler Community,

I am trying to figure out how to create a composite primary key in the wappler database. Can someone point me to the right resource or let me know how I can accomplish this?

Thanks in advance!

#2

You can’t do it directly from the UI.

You would need to create a migration file, undo the changes, edit it and reapply the migration.

https://knexjs.org/#Schema-primary

#3

Not wappler specific per say, but since using knex behind the scenes somewhat relevant :slight_smile:

Not able to find much useful/working info online about composite keys in knex

I just get errors when using .primary. Only get it working with .unique

table.integer("item_id").unsigned();
table.integer("parent_item_id").unsigned();
table.unique(["item_id", "parent_item_id"]);

If there are any examples of using two foreign keys as a composite primary :+1: much appreciated

#4

What errors do you get?

#5

Just redid this in a hurry … have tried many variations
It seems like the placement of the .primary is wrong somehow ?

  return knex.schema.createTable("public.bom_item", function (table) {
    table.integer("parent_item_id").unsigned();
    table.integer("child_item_id").unsigned();
    table.primary(["parent_item_id", "child_item_id"]);
    table.foreign("child_item_id").references("id").inTable("item");
    table.foreign("parent_item_id").references("id").inTable("item");

image

#6

You need to add a contraint name when using composite keys.

image

Will produce.

image
image751×475 20.6 KB

1 Like
#7

I need to be more patient when reading the docs :see_no_evil:
Added a constraint and now it works.
Awesome @JonL

1 Like
#8

I’ve changed this to a feature request as it would save some time to be able to add it from the UI directly. I’m constanly undoing changes to be able to edit the migration file for this, using the right increments in Postgres and adding timestamps columns.

#9

Good idea :+1:
I write all my migration files in VS code due to speed (copy/paste) and small missing features

1 Like
#10

What’s your workflow on vsc for knex for wappler?
How do you handle the naming convention and wappler_migrations table?

#11

Long time since I started a new project so not sure if I remember correctly.

I think I made the first migration in wappler, renamed it to 01_xxx.is and try to control the migration sequence manually. Then mimic that file.

So just I just write/create them in vsc and migrate/rollback from wappler. wappler_migrations table rolls along with that unless I make a stupid move, which has happened a few times :smiley:

1 Like
#12

More or less like I do.

The database manager needs a little bit of love from the team when 4.x is released for advanced uses

2 Likes
#13

I Noticed that when I create a seed file in wappler on a table with a composite primary not all columns are included in the seed.

Can anyone confirm ?