How to clear the Foreign Keys field in SQL

Asked

Viewed 166 times

0

I wonder if there is a way to clear the user_id/store_id reference present in the table

user_store

CREATE TABLE IF NOT EXISTS `user`(
`id` INT(11) PRIMARY KEY AUTO_INCREMENT)

CREATE TABLE IF NOT EXISTS `store`(
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL UNIQUE
)ENGINE INNODB DEFAULT CHAR SET 'utf8' AUTO_INCREMENT=10;

CREATE TABLE IF NOT EXISTS `user_store`(
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`user_id` INT(11) NOT NULL,
`store_id` INT(11) NOT NULL,
CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `user`(`id`),
CONSTRAINT `fk_store_id`
    FOREIGN KEY (`store_id`)
    REFERENCES `store`(`id`)
)ENGINE INNODB DEFAULT CHAR SET 'utf8' AUTO_INCREMENT=10;
php mysql

  • You refer to removing the constraints fk_user_id and fk_store_id or just removing the data (values) from the referenced tables?

  • For example if the user removes his store, user_store should have the normal id and user_id, store_id would be reset

1 answer

0

To remove foreign key references you can do:

ALTER TABLE nome_tabela  DROP FOREIGN KEY nome_constraint;

If you want to delete only the data (records) you can make a delete table in the user_store table:

DELETE FROM nome_tabela

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.