Mysql – How to link several tables

database-designMySQL

I'm a newbie at Database Designing. What I want to do is perform several actions at once.

I have a main users table which holds all the data for a user at a website. Then I have a primary key as the user_id in this table. This user_id is also the referred in various other tables.

So, what I want to do is connect all those table data to the main table so that when the main user is deleted from the users table then all the data related to that user is also deleted. How can I do that?

I know there is a function of foreign keys somewhere but I'm a really amateur programmer.

Best Answer

You may find this question of value: https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete

Highlights:

Your tables should be set up with foreign keys with DELETE CASCADE.

Example:

CREATE TABLE categories (
    id int unsigned not null primary key,
    name varchar(255) default null
);
CREATE TABLE products (
    id int unsigned not null primary key,
    name varchar(255) default null
);

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE ON UPDATE CASCADE
);