Simulation de la déférabilité d’une contrainte

Rare sont les SGBDR a accepter la déférabilité d’une contrainte. Mais que faire si l’on veut à tout prix mettre en place une contrainte circulaire vérifiée ?

En fait il suffit de jouer sur les privilèges en interdisant les mise à jour directe des tables et an passant par des procédures stockée transactionnées.

Démonstration avec MS SQL Server :


-- soit les tables suivantes, créées au sein du schéma S_CIR :
CREATE SCHEMA S_CIR
 
CREATE TABLE T_CLIENT_CLI
(CLI_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CLI PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL,
 CMD_ID           INT NOT NULL CONSTRAINT FK_CLI_CMD FOREIGN KEY  
                               REFERENCES T_COMMANDE_CMD (CMD_ID))
 
CREATE TABLE T_COMMANDE_CMD
(CMD_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CMD PRIMARY KEY,
 CLI_ID           INT NOT NULL CONSTRAINT FK_CMD_CLI FOREIGN KEY  
                               REFERENCES T_CLIENT_CLI (CLI_ID),
 CMD_DATE         DATE NOT NULL DEFAULT GETDATE());

Notez la contrainte circulaire : un client doit pointer vers la dernière commande active (obligatoirement car NOT NULL) et toute commande doit avoir un client.


--> tentative d'insertion
 
BEGIN TRANSACTION
 
DECLARE @CLI_ID INT,  
        @CMD_ID INT;
 
INSERT INTO S_CIR.T_CLIENT_CLI  
VALUES ('DUPONT', NULL);
 
SET @CLI_ID = SCOPE_IDENTITY();
 
INSERT INTO S_CIR.T_COMMANDE_CMD  
VALUES (@CLI_ID, GETDATE());
 
SET @CMD_ID = SCOPE_IDENTITY();
 
UPDATE S_CIR.T_CLIENT_CLI  
SET    CMD_ID = @CMD_ID  
WHERE  CLI_ID = @CLI_ID;
 
COMMIT TRANSACTION

Échec.

-- suppression des objets
ALTER TABLE S_CIR.T_CLIENT_CLI DROP CONSTRAINT FK_CLI_CMD;
DROP TABLE S_CIR.T_COMMANDE_CMD;
DROP TABLE S_CIR.T_CLIENT_CLI;
DROP SCHEMA S_CIR
GO

Nouvelle donne, notez que désormais CMD_ID dans T_CLIENT_CLI est nullable :


CREATE SCHEMA S_CIR
 
CREATE TABLE T_CLIENT_CLI
(CLI_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CLI PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL,
 CMD_ID           INT          CONSTRAINT FK_CLI_CMD FOREIGN KEY  
                               REFERENCES T_COMMANDE_CMD (CMD_ID))
 
CREATE TABLE T_COMMANDE_CMD
(CMD_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CMD PRIMARY KEY,
 CLI_ID           INT NOT NULL CONSTRAINT FK_CMD_CLI FOREIGN KEY  
                               REFERENCES T_CLIENT_CLI (CLI_ID),
 CMD_DATE         DATE NOT NULL DEFAULT GETDATE());
GO

Un client n’est pas un client s’il ne passe pas une première commande. Création d’une procédure d’insertion simultanée d’un client et sa commande au sein d’une transaction :


CREATE PROCEDURE S_CIR.P_I_CLI_CMD @NOM CHAR(32),
                                   @DATE DATE
WITH EXECUTE AS OWNER                              
AS
 
DECLARE @CLI_ID INT,  
        @CMD_ID INT;
 
BEGIN TRANSACTION;
 
INSERT INTO S_CIR.T_CLIENT_CLI  
VALUES (@NOM, NULL);
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
SET @CLI_ID = SCOPE_IDENTITY();
 
INSERT INTO S_CIR.T_COMMANDE_CMD  
VALUES (@CLI_ID, @DATE);
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
SET @CMD_ID = SCOPE_IDENTITY();
 
UPDATE S_CIR.T_CLIENT_CLI  
SET    CMD_ID = @CMD_ID  
WHERE  CLI_ID = @CLI_ID;
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
COMMIT TRANSACTION;
 
RETURN;
 
LBL_ERROR:
ROLLBACK TRANSACTION;
 
GO

Création de l’utilisateur qui va traiter les données


--> assurez vous d'être en authentification mixte (donc SQL)
CREATE LOGIN CNX_CERCLE  
   WITH PASSWORD = 'rantanplan2001',  
        DEFAULT_DATABASE = DB_SQD;
CREATE USER USR_CERCLE
   FOR LOGIN CNX_CERCLE;
GRANT EXECUTE ON S_CIR.P_I_CLI_CMD TO USR_CERCLE;

On l’a doté de l’unique privilège d’exécution de la procédure

Test :

--> emprunt d'identité
EXECUTE AS LOGIN = 'CNX_CERCLE';
-- qui suis-je ?
SELECT USER, SYSTEM_USER;
 
-- puis je voir la table des clients ?
SELECT * FROM S_CIR.T_CLIENT_CLI;
 
-- puis-je insérer dans les deux tables ?
EXECUTE S_CIR.P_I_CLI_CMD 'DUVAL', '20100101'
-- OUI !
-- mais toujours pas voir !
SELECT * FROM S_CIR.T_CLIENT_CLI;
 
REVERT; --> retour à moi !
SELECT * FROM S_CIR.T_CLIENT_CLI;
SELECT * FROM S_CIR.T_COMMANDE_CMD;

CQFD !


Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *

Laisser un commentaire