1-- On Create
2CREATE TABLE tableName (
3 ID INT,
4 SomeEntityID INT,
5 PRIMARY KEY (ID),
6 FOREIGN KEY (SomeEntityID)
7 REFERENCES SomeEntityTable(ID)
8 ON DELETE CASCADE
9);
10
11-- On Alter, if the column already exists but has no FK
12ALTER TABLE
13 tableName
14ADD
15 FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
16
17 -- Add FK with a specific name
18 -- On Alter, if the column already exists but has no FK
19ALTER TABLE
20 tableName
21ADD CONSTRAINT fk_name
22 FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
1CREATE TABLE parent (
2 id INT NOT NULL,
3 PRIMARY KEY (id)
4) ENGINE=INNODB;
5
6CREATE TABLE child (
7 id INT,
8 parent_id INT,
9 INDEX par_ind (parent_id),
10 FOREIGN KEY (parent_id)
11 REFERENCES parent(id)
12 ON DELETE CASCADE
13) ENGINE=INNODB;
1
2
3
4
5 CREATE TABLE products(
6 productId INT AUTO_INCREMENT PRIMARY KEY,
7 productName varchar(100) not null,
8 categoryId INT NOT NULL,
9 CONSTRAINT fk_category
10 FOREIGN KEY (categoryId)
11 REFERENCES categories(categoryId)
12 ON UPDATE CASCADE
13 ON DELETE CASCADE
14) ENGINE=INNODB;
1ALTER TABLE tryholpz_demo07.core_modules
2ADD COLUMN belongs_to_role INT,
3ADD FOREIGN KEY core_modules(belongs_to_role) REFERENCES role_specific_modules_info(id) ON DELETE CASCADE
1ALTER TABLE database.table
2 ADD COLUMN columnname INT DEFAULT(1),
3 ADD FOREIGN KEY fk_name(fk_column) REFERENCES reftable(refcolumn) ON DELETE CASCADE;