title: MYSQL create unique id according to another column (which is a foreign key)
body: <p>In a DB there's a table called <code>decks</code> which looks like this:</p>
  <pre><code>ID | Name
  ------------
  1  | Deck 1
  2  | Deck 2
  3  | Deck 3
  </code></pre>
  <p>And then another table called <code>words</code> which should look like this</p>
  <pre><code>Deck ID | Word ID | Text
  -------------------------------
  1       | 1       | Cat
  2       | 1       | Elephant
  1       | 2       | Dog
  1       | 3       | Elephant
  3       | 1       | Shark
  </code></pre>
  <p>So basically this table should have a Foreign Key which points to the deck and then a key which is set to auto-increment according to the foreign key. Also, note that the ID of this table would be the combination of <code>Deck Id</code> and <code>Word Id</code>. Is this possible in MYSQL 8?</p>
  <p>Basically what I have is this:</p>
  <pre><code>CREATE TABLE my_db.words (
      deck_id INT,
      text VARCHAR(100),
      FOREIGN KEY(deck_id) REFERENCES decks(id)
  );
  </code></pre>
  <p>But no idea how to create this auto-increment field.</p>
  <p>Not sure if it's important but the text column should have no effect whatsoever in the result of this generated ID.</p>