title: Can I use the same foreign key constraint in two different tables?
body: <p>I am trying to create a database for work. I have two different types of users: internal and external. Each type has different properties so I just created two separate tables for them. In my internal table I have the following fields:</p>
  
  <pre><code>f_name VARCHAR(32),
  l_name VARCHAR(32),
  empl_status_id INT,
  admin_grp_id INT,
  reliab_status_id INT,
  supv_id INT
  </code></pre>
  
  <p>And my external table has the following:</p>
  
  <pre><code>f_name VARCHAR(32),
  l_name VARCHAR(32),
  email VARCHAR(32),
  phone VARCHAR(20),
  org_id INT,
  supv_id INT
  </code></pre>
  
  <p>I realize that I could probably create a separate table that contains the names of the users and a foreign key pointing to either internal or external, but that aside, I added a foreign key constraint to my internal table that refers <code>supv_id</code> to another internal user. I called it <code>fk_supv_id</code>. When I tried to do the same for my external user table, I got <code>ERROR 1005 (HY000)</code>. </p>
  
  <p>At first I couldn't figure out what the problem was but when I tried doing it with a different name, it worked (i.e. instead of calling it <code>fk_supv_id</code> just like for internal, I called it <code>fk_xtsupv_id</code>). </p>
  
  <p>So my question is, what is the correct way to this? It's the same foreign key in two different tables. In both cases it refers to an internal user. Is there a way to do this without having two different names? Or should I opt for the table of names idea and add the <code>supv_id</code> constraint to that table along with <code>f_name</code>, <code>l_name</code>, and <code>user_type</code>?</p>
  
  <p>Advice and suggestions are appreciated,</p>
  
  <p>Thanks! :)</p>