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.

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 :

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 * * * * *