0
 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

1
  • ALTER TABLE DROP PRIMARY KEY, ADD PRIMARY KEY (...) -- but that is probably not what you really wanted.
    – Rick James
    Commented May 7, 2021 at 21:45

1 Answer 1

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.

2
  • Thanks, Do you help in making another table, I want column of another table be linked with 1-1 relationship with the phold column of this table. How can I do that? Commented May 6, 2021 at 9:35
  • so far I tried, ``` CREATE TABLE itinery( id INT NOT NULL AUTO_INCREMENT, day VARCHAR(255) NOT NULL, title varchar(255) not null, PRIMARY KEY(title), FOREIGN KEY(title) REFERENCES tours(title) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; ``` I want column-> title in this table be linked with phold column of tours , so that i can relate retrieve data using their title. Commented May 6, 2021 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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