How to alter column to make it primary key when one already exists mysql8?

All we need is an easy explanation of the problem, so here it is.

 id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
 | title      | varchar(255)    | NO   |     | NULL    |                |
 | category   | varchar(255)    | NO   |     | NULL    |                |
 | summary    | varchar(10000)  | NO   |     | NULL    |                |
 | detail     | mediumtext      | NO   |     | NULL    |                |
 | created_at | timestamp       | YES  |     | NULL    |                |   
 | updated_at | timestamp       | YES  |     | NULL    |                |
 | image      | mediumblob      | YES  |     | NULL    |                |
 | image1     | mediumblob      | YES  |     | NULL    |                |
 | image3     | mediumblob      | YES  |     | NULL    |                |
 | phold      | varchar(50)     | NO   |     | NULL    |                |
 +------------+-----------------+------+-----+---------+----------------+

I have this database now,I want to make a one-one relationship with with another table with where I use column ( title ) to link specific data. When I run

mysql> alter table tours add primary key (id,title);

OR

mysql> alter table tours add constraint pk_tours primary key (id, title);

The error:

ERROR 1068 (42000): Multiple primary key defined

on both queries.

I want another table named itinery where I use to link it with former table with help of volumn title ;

I am using mysql 8 with laravel 8.
Thanks

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you would like to have other tables reference just the title field, then you can add a UNIQUE index.

First, edit the tours table to add a NOT NULL constraint to the title field:

ALTER TABLE `tours` MODIFY `title` VARCHAR(255) NOT NULL;

Now you can create the index:

CREATE UNIQUE INDEX `udx_title` ON `tours` (`title`);

Be sure to confirm that all values in title are unique before doing this. Once done, you will be able to have other tables reference the field.

Hat tip to Vérace for pointing out that title could be NULL, which we do not want.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0