mysql constraint foreign key code example
Example 1: mysql change foreign key
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Example 2: mysql add foreign key
-- On Create
CREATE TABLE tableName (
ID INT,
SomeEntityID INT,
PRIMARY KEY (ID),
FOREIGN KEY (SomeEntityID)
REFERENCES SomeEntityTable(ID)
ON DELETE CASCADE
);
-- On Alter, if the column already exists but has no FK
ALTER TABLE
tableName
ADD
FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
-- Add FK with a specific name
-- On Alter, if the column already exists but has no FK
ALTER TABLE
tableName
ADD CONSTRAINT fk_name
FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
Example 3: foreign key mySql
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
Example 4: how to connect database foreign key in mysql
CREATE TABLE products(
productId INT AUTO_INCREMENT PRIMARY KEY,
productName varchar(100) not null,
categoryId INT NOT NULL,
CONSTRAINT fk_category
FOREIGN KEY (categoryId)
REFERENCES categories(categoryId)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;
Example 5: add column mysql with foreign key
ALTER TABLE `TABLE_NAME`
ADD COLUMN `COLUMN_NAME` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `AFTER_COLUMN_NAME`,
ADD FOREIGN KEY `FOREIGN_RELATION_NAME`(`COLUMN_NAME`) REFERENCES `FOREIGN_TABLE`(`FOREIGN_COLUMN`) ON UPDATE SET NULL ON DELETE SET NULL
Example 6: update foreign key value in mysql
SET foreign_key_checks = 0;
UPDATE languages SET id='xyz' WHERE id='abc';
UPDATE categories_languages SET language_id='xyz' WHERE language_id='abc';
SET foreign_key_checks = 1;