Discussion :

[dev-linus]

Bonjour,

Je souhaiterais avoir une clé étrangère dans une table X qui dépendent d'un champ d'un table Y OU Z


Voilà le code que j'ai utilisé qui impose que la clé étrangère appartienne aux DEUX tables. Or, je voudrais qu'elle puisse appartenir à l'une OU à l'autre. Est-ce possible et avez-vous une solution?

Merci de vos réponses par avance...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE Y(
   ChampY INTEGER,
);


CREATE TABLE Z(
   ChampZ INTEGER
);


CREATE TABLE X(
   ChampX INTEGER,

   CONSTRAINT fk_essai1 FOREIGN KEY (ChampX) REFERENCES Y(ChampY),
   CONSTRAINT fk_essai2 FOREIGN KEY (ChampX) REFERENCES Z(ChampZ),
);

[fsmrel]

Bonjour dev-linus,


Il faudra assurer l’intégrité à coups de triggers :

Un trigger pour s’assurer que lors d’un INSERT dans la table X, chaque ligne insérée a bien une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un UPDATE de la table X, chaque ligne modifiée conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un DELETE de la table Y, chaque ligne de la table X conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un DELETE de la table Z, chaque ligne de la table X conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un UPDATE de la table Y, chaque ligne de la table X conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un UPDATE de la table Z, chaque ligne de la table X conserve toujours une référence à Y ou Z.

Exemple avec MS SQL Server.

Création des tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TABLE X
(
        KX               CHAR(04)      NOT NULL
      , Q                CHAR(04)      NOT NULL
      , FKX              CHAR(04)      NOT NULL
    , CONSTRAINT X_PK PRIMARY KEY (KX)
) ;
 
CREATE TABLE Y
(
        KY               CHAR(04)      NOT NULL
      , B                CHAR(04)      NOT NULL
      , C                CHAR(04)      NOT NULL
    , CONSTRAINT Y_PK PRIMARY KEY (KY)
) ;
 
CREATE TABLE Z
(
        KZ               CHAR(04)      NOT NULL
      , H                CHAR(04)      NOT NULL
      , J                CHAR(04)      NOT NULL
    , CONSTRAINT Z_PK PRIMARY KEY (KZ)
) ;

Un échantillon de triggers :

INSERT dans la table X :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TRIGGER X_insert_tr ON X INSTEAD OF INSERT AS
 
    DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
    SET @Count = (SELECT COUNT(*) 
                     FROM   INSERTED
                     WHERE  FKX IN (SELECT KY
                                  FROM   Y
                                  UNION
                                  SELECT KZ
                                  FROM   Z)) ;
    IF @Count = 0 
        BEGIN
            SELECT 'Insert into table X : Erreur d’intégrité' AS X, * FROM INSERTED
            RAISERROR (@Err, 10, 1) 
            RETURN 
        END
    ELSE
        BEGIN
            INSERT INTO X (KX, Q, FKX)
                   SELECT  KX, Q, FKX
                   FROM    INSERTED 
        END

DELETE dans la table Y :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE TRIGGER Y_delete_tr ON Y INSTEAD OF DELETE AS
 
    DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
    SET @Count = (SELECT COUNT(*) 
                  FROM   X JOIN DELETED ON X.FKX = DELETED.KY) ;
 
    IF @Count > 0
        BEGIN
            SET @Count = (SELECT COUNT(*) 
                          FROM   DELETED JOIN Z ON DELETED.KY = Z.KZ) ;
 
            IF @Count = 0 
                BEGIN
                    SET @Err = 'Delete from table Y => Viol d’intégrité'
                    SELECT @Err, * FROM DELETED
                    RAISERROR (@Err, 10, 1) 
                    RETURN		
                END
        END 
    ELSE
        BEGIN
            DELETE FROM Y
                   WHERE KY IN (SELECT DISTINCT KY
                                FROM   DELETED) 
        END

[dev-linus]

Bonjour dev-linus,


Il faudra assurer l’intégrité à coups de triggers :

Un trigger pour s’assurer que lors d’un INSERT dans la table X, chaque ligne insérée a bien une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un UPDATE de la table X, chaque ligne modifiée conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un DELETE de la table Y, chaque ligne de la table X conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un DELETE de la table Z, chaque ligne de la table X conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un UPDATE de la table Y, chaque ligne de la table X conserve toujours une référence à Y ou Z ;

Un trigger pour s’assurer que lors d’un UPDATE de la table Z, chaque ligne de la table X conserve toujours une référence à Y ou Z.

Exemple avec MS SQL Server.

Création des tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TABLE X
(
        KX               CHAR(04)      NOT NULL
      , Q                CHAR(04)      NOT NULL
      , FKX              CHAR(04)      NOT NULL
    , CONSTRAINT X_PK PRIMARY KEY (KX)
) ;
 
CREATE TABLE Y
(
        KY               CHAR(04)      NOT NULL
      , B                CHAR(04)      NOT NULL
      , C                CHAR(04)      NOT NULL
    , CONSTRAINT Y_PK PRIMARY KEY (KY)
) ;
 
CREATE TABLE Z
(
        KZ               CHAR(04)      NOT NULL
      , H                CHAR(04)      NOT NULL
      , J                CHAR(04)      NOT NULL
    , CONSTRAINT Z_PK PRIMARY KEY (KZ)
) ;

Un échantillon de triggers :

INSERT dans la table X :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TRIGGER X_insert_tr ON X INSTEAD OF INSERT AS
 
    DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
    SET @Count = (SELECT COUNT(*) 
                     FROM   INSERTED
                     WHERE  FKX IN (SELECT KY
                                  FROM   Y
                                  UNION
                                  SELECT KZ
                                  FROM   Z)) ;
    IF @Count = 0 
        BEGIN
            SELECT 'Insert into table X : Erreur d’intégrité' AS X, * FROM INSERTED
            RAISERROR (@Err, 10, 1) 
            RETURN 
        END
    ELSE
        BEGIN
            INSERT INTO X (KX, Q, FKX)
                   SELECT  KX, Q, FKX
                   FROM    INSERTED 
        END

DELETE dans la table Y :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE TRIGGER Y_delete_tr ON Y INSTEAD OF DELETE AS
 
    DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
    SET @Count = (SELECT COUNT(*) 
                  FROM   X JOIN DELETED ON X.FKX = DELETED.KY) ;
 
    IF @Count > 0
        BEGIN
            SET @Count = (SELECT COUNT(*) 
                          FROM   DELETED JOIN Z ON DELETED.KY = Z.KZ) ;
 
            IF @Count = 0 
                BEGIN
                    SET @Err = 'Delete from table Y => Viol d’intégrité'
                    SELECT @Err, * FROM DELETED
                    RAISERROR (@Err, 10, 1) 
                    RETURN		
                END
        END 
    ELSE
        BEGIN
            DELETE FROM Y
                   WHERE KY IN (SELECT DISTINCT KY
                                FROM   DELETED) 
        END

Merci pour votre réponse.
je vais essayer de traduire ca en MySQL.

[CinePhil]

Votre problème a peut-être pour source une mauvaise modélisation des données !

Au lieu de X, Y, Z, pourriez-vous nous dire plus contrairement ce qui justifie cette situation étrange ?