This year I’ve been learning about relational databases and how to design them. In order to strenghten my knowledge, I’m trying to design and implement a database using Python and sqlite3.
The database is about a textile company, and, among other thigs, they want to keep information about the following:
- Materials they use to make their products
- Shops where they look for materials
- Some shops (the ones where they do buy materials) are considered suppliers.
- They want to know what suppliers provide what materials
About this last relationship, there are some restrictions:
- A supplier can provide more than one material (Supplier class maximum cardinality many)
- A material can be provided by more than one supplier (Material class maximum carindality many)
- All materials must be provided by at least one supplier (Material class minimum cardinality one)
- All suppliers must provide at least one material (Supplier class minimum cardinality one)
This is how I think the ER diagram looks giving these indications:
Entity-Relation diagram for “Provides” relationship
Given the minimum cardinality one, I think I have to implement integrity restrictions by triggers. This is how I think the logic design (the actual tables in the database) looks:
Logical diagram for “Provides” relationship
With the following integrity restrictions:
IR1. Minimum cardinality one in Material-Provides: every value of the ‘cod_material’ attribute from the Material table must appear at least once as a value of the ‘cod_material’ attribute in the Provides table.
IR2. Minimum cardinality one in Supplier-Provides: every value of the ‘cod_supplier’ attribute from the Supplier table must appear at least once as a value of the ‘cod_supplier’ attribute in the Provides table.
All of this means that, when inserting new suppliers or materials, I will also have to insert what material they provided (in the case of the suppliers) or what supplier has provided it (in the case of the materials).
This is what the triggers I made to take into consideration the integrity restrictions look like (I should also add that I’ve been working with pl-sql, and sqlite uses sql, so I’m not that used to this syntax, and there may be some errors):
CREATE TRIGGER IF NOT EXISTS check_mult_provides_supl
AFTER INSERT ON Supplier
BEGIN
SELECT
CASE WHEN ((SELECT p.cod_supplier FROM Provides p WHERE p.cod_supplier = new.cod_supplier) IS NULL)
THEN RAISE(ABORT, 'Esta tienda no ha provisto aun ningun material')
END;
END;
CREATE TRIGGER IF NOT EXISTS check_mult_provides_mat
AFTER INSERT ON Material
BEGIN
SELECT
CASE WHEN ((SELECT m.cod_material FROM Material m WHERE m.cod_material = new.cod_material) IS NULL)
THEN RAISE(ABORT, 'Este material no ha sido provisto por nadie')
END;
END;
I’ve tried adding new rows to the tables Material and Supplier respectively, and the triggers are working (or at least they’re not allowing me to insert new rows without a row in the Provides table).
This is when I reach the deadlock:
Having the database empty, if I try to insert a row in the tables Material or Supplier the triggers fire and they don’t allow me (because first I need to insert the corresponding row in the table Provides). However, if I try to insert a row in the Provides table, I get a foreign key constraint error (obviously, since that supplier and material are not inserted into their respective tables yet), so basically I cannot insert rows in my database.
The only answers I can think of are not very satisfactory: momentary disabling any constraint (either the foreign key constraint or the integrity one by the trigger) puts the database integrity at risk, since new inserted rows don’t fire the trigger even if this one gets enabled after. The other thing I thought of was relaxing the minimum cardinality restrictions, but I assume a many-to-many relationship with minimum cardinality one restriction should be usual in real databases, so there must be another kind of solutions.
How can I get out of this deadlock? Maybe a procedure (although sqlite doesn’t have store procedures, I think I can make them with the Python API by create_function() in the sqlite3 module) would do the trick?
Just in case, if anyone wants to reproduce this part of the database, here is the code for the creation of the tables (I finally decided to autoincrement the primary key, so the datatype is an integer, as opposed to the ER diagram and the logical diagram which said a datatype character)
CREATE TABLE IF NOT EXISTS Material (
cod_material integer AUTO_INCREMENT PRIMARY KEY,
descriptive_name varchar(100) NOT NULL,
cost_price float NOT NULL
);
CREATE TABLE IF NOT EXISTS Shop (
cod_shop integer AUTO_INCREMENT PRIMARY KEY,
name varchar(100) NOT NULL,
web varchar(100) NOT NULL,
phone_number varchar(12),
mail varchar(100),
address varchar(100)
);
CREATE TABLE IF NOT EXISTS Supplier (
cod_proveedor integer PRIMARY KEY CONSTRAINT FK_Supplier_Shop REFERENCES Shop(cod_shop)
);
CREATE TABLE IF NOT EXISTS Provides (
cod_material integer CONSTRAINT FK_Provides_Material REFERENCES Material(cod_material),
cod_supplier integer CONSTRAINT FK_Provides_Supplier REFERENCES Supplier(cod_supplier),
CONSTRAINT PK_Provides PRIMARY KEY (cod_material, cod_supplier)
);
Advertisement
Answer
I believe that you want a DEFERRED FOREIGN KEY. The triggers, however, will interfere as they would be triggered.
However, you also need to consider the code that you have posted. There is no AUTO_INCREMENT keyword it is AUTOINCREMENT (however you very probably do not do not need AUTOINCREMENT as INTEGER PRIMARY KEY will do all that you required).
If you check SQLite AUTOINCREMENT along with
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
The Supplier table is useless as you have coded it is simply a single column that references a shop with no other data. However, the Provides table references the Supplier table BUT to a non-existent column (cod_supplier).
Coding CONSTRAINT name REFERENCES table(column(s)) doesn’t adhere to the SYNTAX as CONSTRAINT is a table level clause, whilst REFERENCES is a column level clause and this appears to cause some confusion.
I suspect that you may have resorted to Triggers because the FK conflicts weren’t doing anything. By default FK processing is turned off and has to be enabled as per Enabling Foreign Key Support. I don’t believe they are required.
Anyway I believe that the following, that includes changes to overcome the above issues, demonstrates DEFERREED FOREIGN KEYS :-
DROP TABLE IF EXISTS Provides;
DROP TABLE IF EXISTS Supplier;
DROP TABLE IF EXISTS Shop;
DROP TABLE IF EXISTS Material;
DROP TRIGGER IF EXISTS check_mult_provides_supl;
DROP TRIGGER IF EXISTS check_mult_provides_mat;
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS Material (
cod_material integer PRIMARY KEY,
descriptive_name varchar(100) NOT NULL,
cost_price float NOT NULL
);
CREATE TABLE IF NOT EXISTS Shop (
cod_shop integer PRIMARY KEY,
name varchar(100) NOT NULL,
web varchar(100) NOT NULL,
phone_number varchar(12),
mail varchar(100),
address varchar(100)
);
CREATE TABLE IF NOT EXISTS Supplier (
cod_supplier INTEGER PRIMARY KEY, cod_proveedor integer /*PRIMARY KEY*/ REFERENCES Shop(cod_shop) DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE IF NOT EXISTS Provides (
cod_material integer REFERENCES Material(cod_material) DEFERRABLE INITIALLY DEFERRED,
cod_supplier integer REFERENCES Supplier(cod_supplier) DEFERRABLE INITIALLY DEFERRED,
PRIMARY KEY (cod_material, cod_supplier)
);
/*
CREATE TRIGGER IF NOT EXISTS check_mult_provides_supl
AFTER INSERT ON Supplier
BEGIN
SELECT
CASE WHEN ((SELECT p.cod_supplier FROM Provides p WHERE p.cod_supplier = new.cod_supplier) IS NULL)
THEN RAISE(ABORT, 'Esta tienda no ha provisto aun ningun material')
END;
END;
CREATE TRIGGER IF NOT EXISTS check_mult_provides_mat
AFTER INSERT ON Material
BEGIN
SELECT
CASE WHEN ((SELECT m.cod_material FROM Material m WHERE m.cod_material = new.cod_material) IS NULL)
THEN RAISE(ABORT, 'Este material no ha sido provisto por nadie')
END;
END;
*/
-- END TRANSACTION; need to use this if it fails before getting to commit
BEGIN TRANSACTION;
INSERT INTO Shop (name,web,phone_number,mail,address)VALUES('shop1','www.shop1.com','000000000000','shop1@email.com','1 Somewhere Street, SomeTown etc');
INSERT INTO Supplier (cod_proveedor) VALUES((SELECT max(cod_shop) FROM Shop));
INSERT INTO Material (descriptive_name,cost_price)VALUES('cotton',10.5);
INSERT INTO Provides VALUES((SELECT max(cod_material) FROM Material),(SELECT max(cod_supplier) FROM Supplier ));
COMMIT;
SELECT * FROM shop
JOIN Supplier ON Shop.cod_shop = cod_proveedor
JOIN Provides ON Provides.cod_supplier = Supplier.cod_supplier
JOIN Material ON Provides.cod_material = Material.cod_material
;
DROP TABLE IF EXISTS Provides;
DROP TABLE IF EXISTS Supplier;
DROP TABLE IF EXISTS Shop;
DROP TABLE IF EXISTS Material;
DROP TRIGGER IF EXISTS check_mult_provides_supl;
DROP TRIGGER IF EXISTS check_mult_provides_mat;
When run as is then the result is :-
However, if the INSERT into the Supplier is altered to :-
INSERT INTO Supplier (cod_proveedor) VALUES((SELECT max(cod_shop) + 1 FROM Shop));
- i.e. the reference to the shop is not an existing shop (1 greater) then :-
The messages/log are :-
BEGIN TRANSACTION
> OK
> Time: 0s
INSERT INTO Shop (name,web,phone_number,mail,address)VALUES('shop1','www.shop1.com','000000000000','shop1@email.com','1 Somewhere Street, SomeTown etc')
> Affected rows: 1
> Time: 0.002s
INSERT INTO Supplier (cod_proveedor) VALUES((SELECT max(cod_shop) + 1 FROM Shop))
> Affected rows: 1
> Time: 0s
INSERT INTO Material (descriptive_name,cost_price)VALUES('cotton',10.5)
> Affected rows: 1
> Time: 0s
INSERT INTO Provides VALUES((SELECT max(cod_material) FROM Material),(SELECT max(cod_supplier) FROM Supplier ))
> Affected rows: 1
> Time: 0s
COMMIT
> FOREIGN KEY constraint failed
> Time: 0s
That is the deferred inserts were successful BUT the commit failed.
You may wish to refer to SQLite Transaction