Create a relationship between the tables when the table is first created

Question

I use mysql in my database I want to create multiple tables with delete and update relationship

as an example I want to link the profiles table to the users table Associate the mm_id_user column from the users table with the mm_id_profile column from the profiles table

What I actually tried to do is this example and it didn't work for me
https://stackoverflow.com/a/260453/10206991
https://stackoverflow.com/a/9796950/10206991

The code used to create the users table and it succeeds in creating the table

CREATE TABLE users (
    mm_email VARCHAR(255) NOT NULL,
    mm_id_user int NOT NULL AUTO_INCREMENT,
    mm_name VARCHAR(25) NOT NULL,
    mm_password VARCHAR(255) NOT NULL,
    mm_code_reset_pass VARCHAR(100) NOT NULL,
    mm_code_check_email VARCHAR(100) NOT NULL,
    mm_status CHAR(1) NOT NULL DEFAULT '0',
    mm_date_create_account TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mm_date_last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mm_is_login CHAR(1) NOT NULL DEFAULT '0',

    PRIMARY KEY (mm_id_user, mm_email)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;

The code used to create the profiles table. He failed to create the table

CREATE TABLE profiles (
    mm_id_profile VARCHAR(255) NOT NULL,
    mm_image_user TEXT NOT NULL,
    mm_num_stores_all VARCHAR(4) NOT NULL,
    mm_num_stores_exist VARCHAR(4) NOT NULL,
    mm_name_public VARCHAR(25) NOT NULL,
    mm_email_public VARCHAR(255) NOT NULL,
    mm_phone_public VARCHAR(20) NOT NULL,
    mm_location_public TEXT NOT NULL,
    mm_update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX index_profiles(mm_name_public, mm_email_public),
    PRIMARY KEY (mm_id_profile),
    FOREIGN KEY (mm_id_profile) REFERENCES users(mm_email) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;

But if you remove the following line from the profiles table, it will be created But there is definitely no relationship between the two tables

FOREIGN KEY (mm_id_profile) REFERENCES users(mm_email) ON DELETE CASCADE

This is the error message that appears when creating a profiles table

#1005 - Can't create table `mustforu_test`.`profiles` (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

The `mm_email` column needs to be indexed by itself to use it as a foreign key. — Barmar, Jun 17 '21 at 20:49

score 1 · Accepted Answer · answered Jun 17 '21 at 20:52

1

There's no reason to put both mm_id_user and mm_email in the primary key of users. If you want emails to be unique, it should have its own unique index. Your current primary key would allow duplicate emails with different IDs.

CREATE TABLE users (
    mm_email VARCHAR(255) NOT NULL,
    mm_id_user int NOT NULL AUTO_INCREMENT,
    mm_name VARCHAR(25) NOT NULL,
    mm_password VARCHAR(255) NOT NULL,
    mm_code_reset_pass VARCHAR(100) NOT NULL,
    mm_code_check_email VARCHAR(100) NOT NULL,
    mm_status CHAR(1) NOT NULL DEFAULT '0',
    mm_date_create_account TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mm_date_last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mm_is_login CHAR(1) NOT NULL DEFAULT '0',

    PRIMARY KEY (mm_id_user),
    UNIQUE KEY (mm_email)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;

Giving mm_email its own index will allow the foreign key in profiles to work.

answered Jun 17 '21 at 20:52

Barmar

741,623
53
500
612

It works fine But column mm_email He has two keys now Type index and primary is this normal – Mustafa Max Jun 17 '21 at 20:56
`users.mm_email` only has one key in my answer. It's not in the primary key any more. – Barmar Jun 17 '21 at 21:05
Image showing that the column has two keys https://i.ibb.co/m5KR3KD/Capture.png – Mustafa Max Jun 17 '21 at 21:09
Did you change the `PRIMARY KEY` line as I did? – Barmar Jun 17 '21 at 21:09
After removing the column mm_email Its type is 'index'. I want it 'primary' – Mustafa Max Jun 17 '21 at 21:15
The `AUTO_INCREMENT` column has to be the primary key. – Barmar Jun 17 '21 at 21:15
I want two primary keys – Mustafa Max Jun 17 '21 at 21:16
You can only have one primary key. – Barmar Jun 17 '21 at 21:17
But as the picture shows I can have two https://i.ibb.co/m5KR3KD/Capture.png – Mustafa Max Jun 17 '21 at 21:19
No, you had one primary key that contained two columns. That means that the combination of ID and email had to be unique, but they could each be duplicated by themselves. That's almost certainly not what you wanted. – Barmar Jun 17 '21 at 21:21
Yes, I know the point I want to have an id column that I use to name the user folder. Also a mail column, this value cannot be repeated – Mustafa Max Jun 17 '21 at 21:26
I read about the UNIQUE KEY and it has the feature I need which is no repetition Thank you my friend you helped me a lot – Mustafa Max Jun 17 '21 at 21:42

Create a relationship between the tables when the table is first created

1 Answers1