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

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?

CodePudding user response：

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
)