Description:
I checked a bug in mysql: The MySQL doesn't manage foreign keys, recreating them even when they already exist. As if it were a parasitic code, it ignores patterns with "create if it doesn't exist", rendering all control in the bank useless. I will send three procedures and a script that will show the problem with Mysql. I'm coming from SQL_Server and I need this problem to be reviewed as soon as possible:
CREATE DATABASE IF NOT EXISTS BazarDB;
USE BazarDB;
-- PROCEDURE CREATE TABLES
DELIMITER //
CREATE PROCEDURE sp_CreateTables()
BEGIN
-- Tables
CREATE TABLE IF NOT EXISTS Clients (
client_id INT PRIMARY KEY AUTO_INCREMENT,
client_name VARCHAR(80) NOT NULL,
client_email VARCHAR(50) NOT NULL,
client_cpf VARCHAR(30) NOT NULL,
client_phone_number VARCHAR(30) NOT NULL
);
CREATE TABLE IF NOT EXISTS Products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(150) NOT NULL,
product_sku VARCHAR(50) NOT NULL,
product_upc VARCHAR(50) NOT NULL,
product_item_price DECIMAL(18,2) NOT NULL
);
CREATE TABLE IF NOT EXISTS Orders (
order_ID INT PRIMARY KEY AUTO_INCREMENT,
client_id INT NOT NULL,
order_def_id INT NOT NULL,
order_purchase_date DATETIME NOT NULL,
order_payments_date DATETIME NOT NULL,
order_ship_city VARCHAR(50) NOT NULL,
order_ship_state VARCHAR(50) NOT NULL,
order_ship_postal_code VARCHAR(10) NOT NULL,
order_ship_country VARCHAR(30) NOT NULL,
order_currency VARCHAR(10) NOT NULL,
order_ship_service_level VARCHAR(20) DEFAULT 'Standard' CHECK (order_ship_service_level IN ('Standard', 'Express', 'Priority')),
order_status_delivery VARCHAR(20) NOT NULL DEFAULT 'Processing' CHECK (order_status_delivery IN ('Processing', 'Shipped', 'Delivered', 'In Transit', 'Returned')),
FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS OrderItems (
oi_order_ID INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
oi_order_item_id INT NOT NULL,
oi_product_price DECIMAL(18,2) NOT NULL,
oi_quantity_purchased INT NOT NULL,
oi_item_status VARCHAR(20) NOT NULL DEFAULT 'Processing' CHECK (oi_item_status IN ('Processing', 'Shipped', 'Delivered', 'In Transit', 'Returned')),
FOREIGN KEY (order_id) REFERENCES Orders(order_ID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS Purchase_Requests (
pr_id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
pr_supply INT NOT NULL,
pr_quantity INT NOT NULL,
pr_unit_price DECIMAL(18,2) NOT NULL,
pr_total_price DECIMAL(18,2) NOT NULL,
pr_purchase DATETIME NOT NULL,
pr_backorder BIT DEFAULT 0
);
CREATE TABLE IF NOT EXISTS Internal_Storage (
is_id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
is_actual_qte INT DEFAULT 0 NOT NULL,
is_minimal_qte INT DEFAULT 0 NOT NULL,
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS Suppliers (
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_cnpj VARCHAR(30) NOT NULL UNIQUE,
s_email VARCHAR(50) NOT NULL,
s_city VARCHAR(50) NOT NULL,
s_country VARCHAR(30) NOT NULL
);
END//
DELIMITER ;
-- PROCEDURE CREATE FOREIGNS
DELIMITER //
CREATE PROCEDURE sp_CreateForeignKeys()
BEGIN
-- Adicionar chaves estrangeiras
SELECT COUNT(*)
INTO @constraint_count_1
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'BazarDB'
AND TABLE_NAME = 'Orders'
AND CONSTRAINT_NAME = 'FK_OR_CLI';
IF @constraint_count_1 = 0 THEN
ALTER TABLE Orders ADD CONSTRAINT FK_OR_CLI FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE CASCADE ON UPDATE CASCADE;
END IF;
SELECT COUNT(*)
INTO @constraint_count_2
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'BazarDB'
AND TABLE_NAME = 'Internal_Storage'
AND CONSTRAINT_NAME = 'FK_IS_PROD';
IF @constraint_count_2 = 0 THEN
ALTER TABLE Internal_Storage ADD CONSTRAINT FK_IS_PROD FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE;
END IF;
SELECT COUNT(*)
INTO @constraint_count_3
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'BazarDB'
AND TABLE_NAME = 'OrderItems'
AND CONSTRAINT_NAME = 'FK_OI_ORD';
IF @constraint_count_3 = 0 THEN
ALTER TABLE OrderItems ADD CONSTRAINT FK_OI_ORD FOREIGN KEY (order_id) REFERENCES Orders(order_ID) ON DELETE CASCADE ON UPDATE CASCADE;
END IF;
SELECT COUNT(*)
INTO @constraint_count_4
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'BazarDB'
AND TABLE_NAME = 'OrderItems'
AND CONSTRAINT_NAME = 'FK_OI_PROD';
IF @constraint_count_4 = 0 THEN
ALTER TABLE OrderItems ADD CONSTRAINT FK_OI_PROD FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE;
END IF;
END//
DELIMITER ;
-- SCRIPT: VERIFY FOREIGNS
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
CONSTRAINT_SCHEMA = 'BazarDB'
AND REFERENCED_TABLE_NAME IS NOT NULL;
resulting this table:
internal_storage |product_id |FK_IS_PROD |products |product_id
internal_storage |product_id |internal_storage_ibfk_1 |products |product_id
orderitems |order_id |FK_OI_ORD |orders |order_ID
orderitems |product_id |FK_OI_PROD |products |product_id
orderitems |order_id |orderitems_ibfk_1 |orders |order_ID
orderitems |product_id |orderitems_ibfk_2 |products |product_id
orders |client_id |FK_OR_CLI |clients |client_id
orders |client_id |orders_ibfk_1 |clients |client_id
How to repeat:
just run the two procedures above and the script below. There's no way to go wrong. There's no way you can worry about naming a constraint if mysql creates another name without you asking. You lose control of the database, because if this is in a procedure, how many times will mysql recreate the name of the constraint? There must be a solution for this
Suggested fix:
internal_storage |product_id |FK_IS_PROD |products |product_id
orderitems |order_id |FK_OI_ORD |orders |order_ID
orderitems |product_id |FK_OI_PROD |products |product_id
orders |client_id |FK_OR_CLI |clients |client_id
Mysql should block if the constraint is not named: do not create automatically. If anyone misses, let there be an "ENABLE auto_generated_foreign_keys_name" instruction;
Check if the constraint does not exist; if it exists, create it!
Description: I checked a bug in mysql: The MySQL doesn't manage foreign keys, recreating them even when they already exist. As if it were a parasitic code, it ignores patterns with "create if it doesn't exist", rendering all control in the bank useless. I will send three procedures and a script that will show the problem with Mysql. I'm coming from SQL_Server and I need this problem to be reviewed as soon as possible: CREATE DATABASE IF NOT EXISTS BazarDB; USE BazarDB; -- PROCEDURE CREATE TABLES DELIMITER // CREATE PROCEDURE sp_CreateTables() BEGIN -- Tables CREATE TABLE IF NOT EXISTS Clients ( client_id INT PRIMARY KEY AUTO_INCREMENT, client_name VARCHAR(80) NOT NULL, client_email VARCHAR(50) NOT NULL, client_cpf VARCHAR(30) NOT NULL, client_phone_number VARCHAR(30) NOT NULL ); CREATE TABLE IF NOT EXISTS Products ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(150) NOT NULL, product_sku VARCHAR(50) NOT NULL, product_upc VARCHAR(50) NOT NULL, product_item_price DECIMAL(18,2) NOT NULL ); CREATE TABLE IF NOT EXISTS Orders ( order_ID INT PRIMARY KEY AUTO_INCREMENT, client_id INT NOT NULL, order_def_id INT NOT NULL, order_purchase_date DATETIME NOT NULL, order_payments_date DATETIME NOT NULL, order_ship_city VARCHAR(50) NOT NULL, order_ship_state VARCHAR(50) NOT NULL, order_ship_postal_code VARCHAR(10) NOT NULL, order_ship_country VARCHAR(30) NOT NULL, order_currency VARCHAR(10) NOT NULL, order_ship_service_level VARCHAR(20) DEFAULT 'Standard' CHECK (order_ship_service_level IN ('Standard', 'Express', 'Priority')), order_status_delivery VARCHAR(20) NOT NULL DEFAULT 'Processing' CHECK (order_status_delivery IN ('Processing', 'Shipped', 'Delivered', 'In Transit', 'Returned')), FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS OrderItems ( oi_order_ID INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, oi_order_item_id INT NOT NULL, oi_product_price DECIMAL(18,2) NOT NULL, oi_quantity_purchased INT NOT NULL, oi_item_status VARCHAR(20) NOT NULL DEFAULT 'Processing' CHECK (oi_item_status IN ('Processing', 'Shipped', 'Delivered', 'In Transit', 'Returned')), FOREIGN KEY (order_id) REFERENCES Orders(order_ID) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS Purchase_Requests ( pr_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, pr_supply INT NOT NULL, pr_quantity INT NOT NULL, pr_unit_price DECIMAL(18,2) NOT NULL, pr_total_price DECIMAL(18,2) NOT NULL, pr_purchase DATETIME NOT NULL, pr_backorder BIT DEFAULT 0 ); CREATE TABLE IF NOT EXISTS Internal_Storage ( is_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, is_actual_qte INT DEFAULT 0 NOT NULL, is_minimal_qte INT DEFAULT 0 NOT NULL, FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS Suppliers ( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(50) NOT NULL, s_cnpj VARCHAR(30) NOT NULL UNIQUE, s_email VARCHAR(50) NOT NULL, s_city VARCHAR(50) NOT NULL, s_country VARCHAR(30) NOT NULL ); END// DELIMITER ; -- PROCEDURE CREATE FOREIGNS DELIMITER // CREATE PROCEDURE sp_CreateForeignKeys() BEGIN -- Adicionar chaves estrangeiras SELECT COUNT(*) INTO @constraint_count_1 FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'BazarDB' AND TABLE_NAME = 'Orders' AND CONSTRAINT_NAME = 'FK_OR_CLI'; IF @constraint_count_1 = 0 THEN ALTER TABLE Orders ADD CONSTRAINT FK_OR_CLI FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE CASCADE ON UPDATE CASCADE; END IF; SELECT COUNT(*) INTO @constraint_count_2 FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'BazarDB' AND TABLE_NAME = 'Internal_Storage' AND CONSTRAINT_NAME = 'FK_IS_PROD'; IF @constraint_count_2 = 0 THEN ALTER TABLE Internal_Storage ADD CONSTRAINT FK_IS_PROD FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE; END IF; SELECT COUNT(*) INTO @constraint_count_3 FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'BazarDB' AND TABLE_NAME = 'OrderItems' AND CONSTRAINT_NAME = 'FK_OI_ORD'; IF @constraint_count_3 = 0 THEN ALTER TABLE OrderItems ADD CONSTRAINT FK_OI_ORD FOREIGN KEY (order_id) REFERENCES Orders(order_ID) ON DELETE CASCADE ON UPDATE CASCADE; END IF; SELECT COUNT(*) INTO @constraint_count_4 FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'BazarDB' AND TABLE_NAME = 'OrderItems' AND CONSTRAINT_NAME = 'FK_OI_PROD'; IF @constraint_count_4 = 0 THEN ALTER TABLE OrderItems ADD CONSTRAINT FK_OI_PROD FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE; END IF; END// DELIMITER ; -- SCRIPT: VERIFY FOREIGNS SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = 'BazarDB' AND REFERENCED_TABLE_NAME IS NOT NULL; resulting this table: internal_storage |product_id |FK_IS_PROD |products |product_id internal_storage |product_id |internal_storage_ibfk_1 |products |product_id orderitems |order_id |FK_OI_ORD |orders |order_ID orderitems |product_id |FK_OI_PROD |products |product_id orderitems |order_id |orderitems_ibfk_1 |orders |order_ID orderitems |product_id |orderitems_ibfk_2 |products |product_id orders |client_id |FK_OR_CLI |clients |client_id orders |client_id |orders_ibfk_1 |clients |client_id How to repeat: just run the two procedures above and the script below. There's no way to go wrong. There's no way you can worry about naming a constraint if mysql creates another name without you asking. You lose control of the database, because if this is in a procedure, how many times will mysql recreate the name of the constraint? There must be a solution for this Suggested fix: internal_storage |product_id |FK_IS_PROD |products |product_id orderitems |order_id |FK_OI_ORD |orders |order_ID orderitems |product_id |FK_OI_PROD |products |product_id orders |client_id |FK_OR_CLI |clients |client_id Mysql should block if the constraint is not named: do not create automatically. If anyone misses, let there be an "ENABLE auto_generated_foreign_keys_name" instruction; Check if the constraint does not exist; if it exists, create it!