Allow to manually sepecify column as Primary column option

[lafriks]

Currently there is no way to create BigID or ID column and not specifying it as primary key (it will always be created as primary key) but there are cases where you need to combine id and additional column as primary key - this is needed for partitioned tables in PostgreSQL

[Fs02]

can you share what the final API will looks like?
I'm also curious on why not use normal fields and then PrimaryKeys?

[lafriks]

Primary problem is not that much about being able to define BigID or ID columns (that ar BIGSERIAL or SERIAL in postgresql) and set them not being primary key. For example with my proposed API you can define something like:

This would be needed to define partitioned table in PostgreSQL:

schema.CreateTable("audit", func(t *rel.Table) {
  t.BigID("id", rel.Primary(false))
  t.DateTime("timestamp", rel.Required(true))
  // other fields
  t.PrimaryKeys([]string{"id", "timestamp"})
  t.Options = `PARTITION BY RANGE ("timestamp")`
 })

or you could define other type column as primary with alt syntax:

schema.CreateTable("client", func(t *rel.Table) {
  t.String("id", rel.Primary(true), rel.Limit(64))
  // other fields
})

that would be equivalent to using:

schema.CreateTable("client", func(t *rel.Table) {
  t.String("id", rel.Limit(64))
  // other fields
  t.PrimaryKey("id")
})

[Fs02]

as enhancements of above case I'm thinking to implicitly set Primary to false on column if t.PrimaryKeys is called 🤔
so your example can be shortened to:

schema.CreateTable("audit", func(t *rel.Table) {
  t.BigID("id") // no need to specify Primary(false), will automatically set by `t.PrimaryKeys`
  t.DateTime("timestamp", rel.Required(true))
  // other fields
  t.PrimaryKeys([]string{"id", "timestamp"})
  t.Options = `PARTITION BY RANGE ("timestamp")`
 })

what do you think?

[Fs02]

@lafriks can you update above prs?
I've tagged rel@v0.29.0 and sql@v0.6.0

[lafriks]

updated, can be merged, closing issue :)