0

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.

0

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
);

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.