'KEY' in Prisma ORM

Question

I'm trying to create a prisma model based on a schema.sql

Look at this one:

model market_history {
  id          Int     @id @default(autoincrement())
  player_id   Int
  sale        Int     @default(0) @db.TinyInt
  itemtype    Int     @db.UnsignedSmallInt
  amount      Int     @db.UnsignedSmallInt
  price       BigInt  @default(0) @db.UnsignedBigInt
  expires_at  BigInt  @db.UnsignedBigInt
  inserted    BigInt  @db.UnsignedBigInt
  state       Int     @db.UnsignedTinyInt
  f_player_id players @relation(fields: [player_id], references: [id])
}

And the result is:

CREATE TABLE `market_history` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `player_id` INTEGER NOT NULL,
    `sale` TINYINT NOT NULL DEFAULT 0,
    `itemtype` SMALLINT UNSIGNED NOT NULL,
    `amount` SMALLINT UNSIGNED NOT NULL,
    `price` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `expires_at` BIGINT UNSIGNED NOT NULL,
    `inserted` BIGINT UNSIGNED NOT NULL,
    `state` TINYINT UNSIGNED NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

But the "original" code is:

CREATE TABLE `market_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `player_id` int NOT NULL,
  `sale` tinyint(1) NOT NULL DEFAULT '0',
  `itemtype` int unsigned NOT NULL,
  `amount` smallint unsigned NOT NULL,
  `price` int unsigned NOT NULL DEFAULT '0',
  `expires_at` bigint unsigned NOT NULL,
  `inserted` bigint unsigned NOT NULL,
  `state` tinyint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `player_id` (`player_id`,`sale`),
  CONSTRAINT `market_history_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1300 DEFAULT CHARSET=latin1;

Look at that KEY player_id (player_id,sale)

I'd like to know if is it possible to have that KEY thing on Prisma

score 2 · Accepted Answer · answered Jun 05 '22 at 09:33

I wasn't familiar with the KEY keyword but according to this response it's the same as an index in MySQL. So, you can get the same behaviour by defining a multi-field (aka composite) index on the market_history model:

model market_history {
  id          Int     @id @default(autoincrement())
  player_id   Int
  sale        Int     @default(0) @db.TinyInt
  itemtype    Int     @db.UnsignedSmallInt
  amount      Int     @db.UnsignedSmallInt
  price       BigInt  @default(0) @db.UnsignedBigInt
  expires_at  BigInt  @db.UnsignedBigInt
  inserted    BigInt  @db.UnsignedBigInt
  state       Int     @db.UnsignedTinyInt
  f_player_id players @relation(fields: [player_id], references: [id])

  @@index([player_id, sale])
}

'KEY' in Prisma ORM

1 Answers1