Create composite primary key with a name so I can reference it-CodePudding

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)
);

CodePudding user response：

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

CodePudding user response：

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.