Mysql – Make primary key and unique id as foriegn key

MySQL

I have two tables as follows:

UserProfile

CREATE TABLE IF NOT EXISTS USER_PROFILE (
  id                BIGINT       NOT NULL AUTO_INCREMENT,
  user_id           BIGINT       NOT NULL,      
  name              VARCHAR(100) NOT NULL,
  username          VARCHAR(100) NOT NULL,
  mobile            BIGINT       NOT NULL,      
  description       VARCHAR(100),
  created_date      DATETIME,
  modified_date     DATETIME,
  PRIMARY KEY (id),
  UNIQUE (user_id)
);

UserDescriptionHistory

CREATE TABLE IF NOT EXISTS USER_DESCRIPTION_HISTORY (
  id           BIGINT        NOT NULL   AUTO_INCREMENT,
  user_id      BIGINT        NOT NULL,
  description  VARCHAR(100),
  dt_datetime  DATETIME      NOT NULL   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id)  
);

SHOW CREATE TABLE UserProfile

CREATE TABLE `CYCLOS_USER_PROFILE` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `mobile` (`mobile`)
   ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

I read that the best practice is to reference pk from the parent table. But I also need the unique constraint from the parent table which is the user_id. So can I reference both pk and unique constraints on foreign key? If yes, how can I do that? Thanks in advance!

Best Answer

The first table, user_profile has two unique constraints, the PRIMARY KEY on (id) and the UNIQUE on the non-nullable (user_id).

That makes it possible to reference either of them in FOREIGN KEY constraints from other tables. You can use what you have, to reference the PK:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id) 

or, to reference the UNIQUE column:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (user_id) 

There is no need to reference both of them.