Question 22150 in Mysql Question posted in
The official MySQL documentation can be found here.

Create composite primary key with a name so I can reference it – Mysql

At a workplace they recycle punchcard ids (for some strange reason). So it is common to have past employees clashing with current employees. As a workaround I want to have employee punchcard id, employee name+surname as the unique primary key (fingers crossed, perhaps add date-of-birth and even passport if available). That can be accomplished with
PRIMARY KEY (pid,name,surname).

The complication is that another table now wants to reference an employee by its above primary key.

Alas, said PK has no name! How can I reference it?

I tried these but no joy:

PRIMARY KEY id (pid, name, surname),

INDEX id (pid, name, surname),
PRIMARY KEY id,

INDEX id (pid, name, surname) PRIMARY KEY,

Can you advise on how to achieve this or even how to reference a composite primary key?

Update:
The table to store employees is em.
The table which references an employee is co (a comment made by an employee).

Ideally I would use pid (punchcard id) as the unique id of each employee. But since pids are recycled, this is not unique. And so I resorted to creating a composite key or an index which will be unique and can reference that as a unique employee id. Below are the 2 tables without the composite key. For brevity, I abbreviated table names and omitted surname etc. So the question is, how can I reference an employee whose id is composite from another table co.

CREATE TABLE em (
  pid INT NOT NULL,
  name VARCHAR(10) NOT NULL
);
CREATE TABLE co (
  id INT primary key auto_increment,
  em INT,
  content VARCHAR(100) NOT NULL,
  constraint co2em_em_fk foreign key (em) references em(pid)
);
Tags:

2

Answers


  1. 0

    If another table wants to reference this one by a composite key, you don’t need it to have a name – just the list of fields will do. E.g.

       CREATE TABLE other_table (
     ID INT PRIMARY KEY AUTO_INCREMENT,
     pid *defintion*,
     name *defintion*,
     surname *defintion*,
     ..., -- other fields, keys etc.
     FOREIGN KEY (pid, name, surname) REFERENCES employees(pid, name, surname)
   );

    UPD: If you expect that the set of the fields inside PK might change and you can’t make a simpler PK (auto-increment integer for example) for the original table, then your best bet might be something like this:

        CREATE TABLE employee_key (
      ID INT PRIMARY KEY AUTO_INCREMENT,
      pid *defintion*,
      name *defintion*,
      surname *defintion*,
      FOREIGN KEY (pid, name, surname) REFERENCES employees(pid, name, surname)
    );
  
    -- and then reference the employees from other tables by the key from employee_key:
    CREATE TABLE other_table(
      ID INT AUTO_INCREMENT PRIMARY KEY,
      employee_id INT NOT NULL,
      ... -- other fields, indexes, etc...
      FOREIGN KEY (employee_id) REFERENCES employee_key(ID)
    );

    Then if you have a change in employee table PK, you’ll only need to update employee itself and employee_key, any other tables would stay as is.

    If you CAN, however, change the original employees table, I would recommend something like this:

    CREATE TABLE employees(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  pid *defintion*,
  name *defintion*,
  surname *defintion*,
  ... -- other fields, keys, etc.
  UNIQUE KEY (pid, name, surname)
);

    Then you’ll have to maintain the logic of generating new pid’s in your code, though, or have them in some side table.

    UPD2: Regarding inserts and updates.

    As for inserts: you need to insert these explicitly – otherwise how would you expect the relation to be established? If you’re using an ORM library to communicate with your database, then it might provide you with the methods to specify linked objects without explicitly adding the IDs, but otherwise to insert a row into employees, employee_key and other_table you need to first INSERT INTO employees(...) ;, then get perform a separate INSERT for the employee_key (knowing the key fields you’ve just added to employees), get the auto-generated key from employee_key and then use that to perform inserts to any other tables.

    You might simplify all this by writing an AFTER INSERT trigger for employees table (that would automatically create a row in employee_key) and/or performing your inserts via a stored procedure (that will even return back the key of the newly inserted row in employee_key). But still this work needs to be done, MySQL won’t do it for you by default.

    Updates are a bit easier, since you can specify ON UPDATE CASCADE when adding the foreign key – in that case a change to one of the fields in the employees will automatically trigger the same change in any tables that reference employees by this key.

    Login or Signup to reply.
  2. 0

    You would define it

      CONSTRAINT id 
   PRIMARY KEY (pid, name, surname)

    But you should read more about how MySQL uses INDEXES and how to optimize them

    https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search