A propos des contraintes SQL

Le
Steve B.
Hello,

J'ai dans une BDD quatre tables dont voici une structure simplifiée :

TABLE1

Table1ID (int) PK


TABLE2

Table2ID (int) PK
name

TABLE3

Table3ID (int) PK
table2ID --> FK vers table2

TABLE_ASSO
-
Table_assoID (int) PK
table1ID --> FK vers table1
table3ID --> FK vers table3


En gros, TABLE_ASSO permet d'associer un table3 à un table1 (relation n à
n).

Mon problème est que d'un point de vue métier, il ne peut y avoir plus d'une
association entre un élément de table 1 et un élément de table 2 (au travers
de table 3) (je suis clair là ?)
Si deux enregistrements dans table 3 ont le meme table2ID, il n'ont pas le
droit d'etre associé au meme table 1.

Techniquement, cette requete SQL ne doit rien renvoyer :

SELECT
COUNT(TABLE_ASSO.Table3ID) AS [Times associated],
TABLE1.Table1ID,
TABLE3.Table3ID

FROM TABLE3 INNER JOIN
TABLE_ASSO ON TABLE3.Table3ID = TABLE_ASSO.Table3ID
INNER JOIN
TABLE1 ON TABLE_ASSO.Table1ID = TABLE1.Table1ID
GROUP BY TABLE1.Table1ID, TABLE3.Table2ID
HAVING (COUNT(TABLE_ASSO.Table3ID) > 1)

Cette requête compte le nombre de fois qu'un table 2 est associé à un table
1 et affiche les doubles associations.

Voici enfin ma question :
Comment puis je ajouter dans la base de données (simplement de préférence)
une contrainte (ou autre chose) qui me permet d'éviter que le cas de double
association arrive ? La doc sur les contraintes n'est pas suffisament
claire, ici il s'agit d'une vérification complexe et je ne sais pas comment
la mettre en oeuvre

Merci d'avance,
Steve
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Fred BROUARD
Le #11856881
Bonjour,


Steve B. a écrit :
Hello,

J'ai dans une BDD quatre tables dont voici une structure simplifiée :

TABLE1
---------
Table1ID (int) PK


TABLE2
---------
Table2ID (int) PK
name

TABLE3
---------
Table3ID (int) PK
table2ID --> FK vers table2

TABLE_ASSO
----------------
Table_assoID (int) PK
table1ID --> FK vers table1
table3ID --> FK vers table3


En gros, TABLE_ASSO permet d'associer un table3 à un table1 (relation n à
n).

Mon problème est que d'un point de vue métier, il ne peut y avoir plus d'une
association entre un élément de table 1 et un élément de table 2 (au travers
de table 3) (je suis clair là ?)
Si deux enregistrements dans table 3 ont le meme table2ID, il n'ont pas le
droit d'etre associé au meme table 1.

Techniquement, cette requete SQL ne doit rien renvoyer :

SELECT
COUNT(TABLE_ASSO.Table3ID) AS [Times associated],
TABLE1.Table1ID,
TABLE3.Table3ID

FROM TABLE3 INNER JOIN
TABLE_ASSO ON TABLE3.Table3ID = TABLE_ASSO.Table3ID
INNER JOIN
TABLE1 ON TABLE_ASSO.Table1ID = TABLE1.Table1ID
GROUP BY TABLE1.Table1ID, TABLE3.Table2ID
HAVING (COUNT(TABLE_ASSO.Table3ID) > 1)

Cette requête compte le nombre de fois qu'un table 2 est associé à un table
1 et affiche les doubles associations.

Voici enfin ma question :
Comment puis je ajouter dans la base de données (simplement de préférence)
une contrainte (ou autre chose) qui me permet d'éviter que le cas de double
association arrive ? La doc sur les contraintes n'est pas suffisament
claire, ici il s'agit d'une vérification complexe et je ne sais pas comment
la mettre en oeuvre



Créez un trigger comme suit :

CREATE TRIGGER E_IU_ASSO
ON TABLE_ASSO
FOR UPDATE, INSERT
AS
IF EXISTS(SELECT 1
FROM TABLE3 T3
INNER JOIN TABLE_ASSO TA
ON T3.Table3ID = TA.Table3ID
INNER JOIN TABLE1 T1
ON TA.Table1ID = T1.Table1ID
GROUP BY T1.Table1ID, T3.Table2ID
HAVING COUNT(TA.Table3ID) > 1)
BEGIN
ROLLBACK
RAISERROR('La cardinalité entre les tables 1 et 3 ne peut dépasser
1', 16, 2)
END
GO

Pour la syntaxe des triggers, lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/sqlserver/transactsql/#L5

A +


Merci d'avance,
Steve






--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Steve B.
Le #11856831
Merci pour cette info...

J'ai finalement réussi en utilisant une fonction scalaire
(numberofassociation avec deux paramètres en entrées), puis l'expression
suivante en contrainte : dbo.NumberOfAssocation(Table3ID, Table1ID) <= 1

Merci !


"Fred BROUARD" news:
Bonjour,


Steve B. a écrit :
Hello,

J'ai dans une BDD quatre tables dont voici une structure simplifiée :

TABLE1
---------
Table1ID (int) PK


TABLE2
---------
Table2ID (int) PK
name

TABLE3
---------
Table3ID (int) PK
table2ID --> FK vers table2

TABLE_ASSO
----------------
Table_assoID (int) PK
table1ID --> FK vers table1
table3ID --> FK vers table3


En gros, TABLE_ASSO permet d'associer un table3 à un table1 (relation n à
n).

Mon problème est que d'un point de vue métier, il ne peut y avoir plus
d'une association entre un élément de table 1 et un élément de table 2
(au travers de table 3) (je suis clair là ?)
Si deux enregistrements dans table 3 ont le meme table2ID, il n'ont pas
le droit d'etre associé au meme table 1.

Techniquement, cette requete SQL ne doit rien renvoyer :

SELECT
COUNT(TABLE_ASSO.Table3ID) AS [Times associated],
TABLE1.Table1ID,
TABLE3.Table3ID

FROM TABLE3 INNER JOIN
TABLE_ASSO ON TABLE3.Table3ID = TABLE_ASSO.Table3ID
INNER JOIN
TABLE1 ON TABLE_ASSO.Table1ID = TABLE1.Table1ID
GROUP BY TABLE1.Table1ID, TABLE3.Table2ID
HAVING (COUNT(TABLE_ASSO.Table3ID) > 1)

Cette requête compte le nombre de fois qu'un table 2 est associé à un
table 1 et affiche les doubles associations.

Voici enfin ma question :
Comment puis je ajouter dans la base de données (simplement de
préférence) une contrainte (ou autre chose) qui me permet d'éviter que le
cas de double association arrive ? La doc sur les contraintes n'est pas
suffisament claire, ici il s'agit d'une vérification complexe et je ne
sais pas comment la mettre en oeuvre



Créez un trigger comme suit :

CREATE TRIGGER E_IU_ASSO
ON TABLE_ASSO
FOR UPDATE, INSERT
AS
IF EXISTS(SELECT 1
FROM TABLE3 T3
INNER JOIN TABLE_ASSO TA
ON T3.Table3ID = TA.Table3ID
INNER JOIN TABLE1 T1
ON TA.Table1ID = T1.Table1ID
GROUP BY T1.Table1ID, T3.Table2ID
HAVING COUNT(TA.Table3ID) > 1)
BEGIN
ROLLBACK
RAISERROR('La cardinalité entre les tables 1 et 3 ne peut dépasser 1',
16, 2)
END
GO

Pour la syntaxe des triggers, lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/sqlserver/transactsql/#L5

A +


Merci d'avance,
Steve






--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************


Publicité
Poster une réponse
Anonyme