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