0

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!

  • Why do you need 2 columns (id and user_id) in the user_profile table? The cleanest option is to have only one. Whether it is PRIMARY KEY or UNIQUE NOT NULL doesn't matter much. – ypercubeᵀᴹ Mar 15 at 9:20
  • It is a legacy code. I do not know why two columns have been created. Anyway at present I have implemented as per pk. But I would like to know whether I can create an fk constraint using UNIQUE. – Lax_Sam Mar 15 at 9:23
  • Yes, you can. No problem at all – ypercubeᵀᴹ Mar 15 at 9:26
  • Could you please tell how I can do that? – Lax_Sam Mar 15 at 9:28
0

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.

  • If I refer to UNIQUE field as per the second method I get this error: Cannot add foreign key constraint – Lax_Sam Mar 15 at 9:36
  • It works when I try it in dbfiddle: dbfiddle.uk/… – ypercubeᵀᴹ Mar 15 at 9:51
  • I am still getting the same error in mysql in my system. – Lax_Sam Mar 15 at 10:15
  • And you are using the exact statement that you posted, only changing the REFERENCES USER_PROFILE (id) to REFERENCES USER_PROFILE (user_id) in the FK? – ypercubeᵀᴹ Mar 15 at 10:21
  • 1
    Oh ok. I was just seeing the code for create table. In that the UNIQUE KEY was user_id. I did not know that it was changed later. From next time I should check the history of changes of a table before asking here! – Lax_Sam Mar 15 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.