MySQL Add 2 Primary Keys to the Same table, then reference to them in another table

Question

I have two tables: customers_card and card_info In the customers_card table I have two primary keys: id and cid. In the card_info table I have 1 primary key: id. I want to add 2 foreign keys to the card_info table but I got an error when I try to reference to the card_info.cid

customers_card table

card_info table

CREATE TABLE card_info (
   id INT(11) PRIMARY KEY AUTO_INCREMENT,
   uid INT(11) NOT NULL,
   cid VARCHAR(255) NOT NULL,
   number BIGINT NOT NULL,
   holder VARCHAR(255) NOT NULL,
   type VARCHAR(255) NOT NULL,
   provider VARCHAR(255) NOT NULL,
   FOREIGN KEY (uid)
      REFERENCES customers (id)
      ON DELETE CASCADE,
   FOREIGN KEY (cid)
      REFERENCES customers__card (cid)
      ON DELETE CASCADE
);

And the error I get when I try to create the card_info table:

customers_card

CREATE TABLE `customers_card` (
 `id` int NOT NULL,
 `uid` int NOT NULL,
 `cid` varchar(255) COLLATE utf8mb4_hungarian_ci NOT NULL,
 `cardname` varchar(255) COLLATE utf8mb4_hungarian_ci NOT NULL,
 `cardnum` int NOT NULL,
 `expiry` date NOT NULL,
 `cvc` int NOT NULL,
 `value` int NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD PRIMARY KEY (`id`,`cid`),
  ADD CONSTRAINT `customers__card_ibfk_1` FOREIGN KEY (`uid` REFERENCES `customers` (`id`) ON DELETE CASCADE
)

My summarized question: How can I create a foreign key in the card_info.cid that references to the customers_card.cid without making the customers_card.cid a primary key?

You cannot have two primary keys on a table. You can have one PK and a secondary key, or you can have a composite PK (with more than one column). Please provide the `CREATE TABLE customers_card (...)` command. — The Impaler, Jun 21 '22 at 01:48
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) Please clarify via edits, not comments. Please ask 1 specific researched non-duplicate question. [mre] [ask] [Help] — philipxy, Jun 21 '22 at 07:42
Since the error message (and the documentation and/or an introduction to SQL & googling) tells you that having 2 PKs doesn't make sense, what are you trying to accomplish in other words than that you want 2 PKs? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. And what did researching in those other words tell you about your problem? — philipxy, Jun 21 '22 at 07:47
Re (re)search success: Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. — philipxy, Jun 21 '22 at 07:48
Does this answer your question? [MySQL Error 1822: Failed to add foreign key constraint; missing index for contraint BUT index exists](https://stackoverflow.com/questions/43511183/mysql-error-1822-failed-to-add-foreign-key-constraint-missing-index-for-contra) — philipxy, Jun 21 '22 at 08:13

score 2 · Accepted Answer · answered Jun 21 '22 at 10:20

Don't use two primary keys. Set the id to UNIQUE and keep the AUTO_INCREMENT, and set the primary key to the cid, so you can reference to them.

CREATE TABLE `customers_card` (
  `id` int UNIQUE AUTO_INCREMENT,
  `uid` int NOT NULL,
  `cid` varchar(255) PRIMARY KEY,
  `cardname` varchar(255) COLLATE utf8mb4_hungarian_ci NOT NULL,
  `cardnum` int NOT NULL,
  `expiry` date NOT NULL,
  `cvc` int NOT NULL,
  `value` int NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   ADD CONSTRAINT `customers__card_ibfk_1` FOREIGN KEY (`uid` REFERENCES `customers` (`id`) ON DELETE CASCADE
)

MySQL Add 2 Primary Keys to the Same table, then reference to them in another table

1 Answers1