Early on in a local project, I kept finding out of date, or strange references to setting up Foreign Keys in various complicated situations in MySQL, this resulted in not only me being confused, but in constantly needing to drop tables and rebuild them. Which was annoying.
Using Node as the backend framework, it dawned on me, that I could just have three terminals open, one running the Node server, one using the mysql> prompt, and the third running terminal commands to rebuild the database using the schema.sql file. Failing quickly is always better than failing slowly.
So the order went:
1. Test something, screw up database.
2. Terminal 1:
mysql> drop database people;
3. Terminal 2:
$: mysql -u root < schema.sql
4. Terminal 3: execute stuff on server
5. Go back to 1.
Here's an example schema file, in case you came here just looking for Foreign Key syntax:
CREATE DATABASE people; USE people; CREATE TABLE parents( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NULL DEFAULT NULL, PRIMARY KEY(id) ); CREATE TABLE locations( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NULL DEFAULT NULL, PRIMARY KEY(id) ); CREATE TABLE children( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(140) NULL DEFAULT NULL, parentId INT, locationId INT, PRIMARY KEY(id), INDEX(parentId, locationId), FOREIGN KEY (parentId) REFERENCES parents(id) ON DELETE CASCADE, FOREIGN KEY (locationId) REFERENCES locations(id) ON DELETE CASCADE );