title: RDBMS: Generating a locally Unique Key for a Relational Table with Foreign Key
body: <p>Below is the schema of the database.</p>
  <p><a href="https://i.stack.imgur.com/aVVuE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aVVuE.png" alt="database schema" /></a></p>
  <ol>
  <li>There are multiple screenplays each identified by a globally unique key.</li>
  <li>Multiple scenes can exist for a screenplay and linked by foreign key.</li>
  </ol>
  <p>The obvious choice for me for <strong>scene_id</strong> of Scene table was to use an auto-increment Integer field type which will ensure each scene in Scene table has a globally unique key across all the multiple screenplays.</p>
  <p>Now, the query is:</p>
  <ol>
  <li>What is the best way for generating <strong>scene_id</strong> for Scene table?</li>
  <li>Isn't keeping a globally unique key an overkill when the
  <strong>scene_id</strong> needs to be unique only within a particular Screenplay?</li>
  </ol>
  <p>A sample table</p>
  <pre><code>+----------+------------+-----------------+------+
  | Scene_Id | Scene_Name |     Scrn_ID     |      |
  +----------+------------+-----------------+------+
  |        1 |            | Opening Scene   | 1001 |
  |        2 |            | Climax Scene    | 1001 |
  |        3 |            | End Credits     | 1001 |
  |        1 |            | Opening Scene 1 | 1002 |
  |        2 |            | Character Intro | 1002 |
  |        3 |            | Conflict        | 1002 |
  |        4 |            | Climax Scene    | 1002 |
  +----------+------------+-----------------+------+
  </code></pre>