How to drop a foreign key-CodePudding

I am trying to figure out how to drop a foreign key that references another table.

CREATE TABLE Toy (
NUMBER int(5) NOT NULL,
SIZE int(5) NOT NULL,
TYPE varchar(15) NOT NULL,
PRICE int(10) NOT NULL,
PRIMARY KEY(NUMBER),
foreign key (NUMBER) references Warehouse(NUMBER));

In the examples I have seen, they had something like "ADD CONSTRAINT [column name]", but I do not have this. I'm unsure what direction to take.

CodePudding user response：

Constraints and indexes have names; if you don't specify one when you add them, they get assigned a name anyway, and you need the name to drop them.

Do show create table Toy;; you will see a line like:

CONSTRAINT `somename` FOREIGN KEY (`NUMBER`) REFERENCES `Warehouse` (`NUMBER`)

To remove it, do:

ALTER TABLE Toy DROP CONSTRAINT somename

CodePudding user response：

Show foreign keys

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;