How Colud I set foreign key on MySQL?

[kazu728]

I'm not sure that the following migration sample code doesn't generate correct SQL about reference foreign key on MySQL. https://github.com/koskimas/kysely#migrations

Environment

• Kysely version: 0.8.1
• DB Engine: MySQL5.7.35

await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', (col) => col.increments().primaryKey())
  .addColumn('name', 'varchar', (col) => col.notNull().unique())
  .addColumn('owner_id', 'integer', (col) =>
    col.references('person.id').onDelete('cascade')
  )
  .addColumn('species', 'varchar')
  .execute()

I changed the column type from integer to bigint for MySQL.

The generated sql

CREATE TABLE `pet` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(50) NOT NULL UNIQUE,
  `owner_id` bigint REFERENCES `person` (`id`) ON DELETE CASCADE,
  `species` varchar(50))    

One of expected SQL

CREATE TABLE `pet` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(50) NOT NULL UNIQUE,
  `owner_id` bigint,
  `species` varchar(50),
  FOREIGN KEY (owner_id) REFERENCES person (id) ON DELETE CASCADE)

[koskimas]

Kysely just builds the SQL, and doesn't try to create the same API for all dialects. If the foreign keys need to be defined on the table level, call addForeignKeyConstraint for the table builder instead of using the column builder:

await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', (col) => col.increments().primaryKey())
  .addColumn('name', 'varchar', (col) => col.notNull().unique())
  .addColumn('owner_id', 'integer')
  .addColumn('species', 'varchar')
  .addForeignKeyConstraint(
    'owner_id_foreign_key',
    ['owner_id'],
    'person',
    ['id'],
    (constraint) => constraint.onDelete('cascade')
  )
  .execute()

[koskimas]

I added some documentation about this.