requete et relation

Bonsoir,

Citation:

Envoyé par gentelmand

comment savoir avec une requéte s'il existe une relation ou pas entre deux tables passées comme paramétre et qui est le champ qui lie ces deux tables

Je vais supposer que le terme "relation" est synonyme de lien direct entre deux tables, et que l'intégrité référentielle est en place.
Dans ces conditions, supposons donc que l’on ait, par exemple, deux tables Matiere et Niveau, définies au moyen des instructions suivantes :

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE Matiere (
  Matiere_Id INTEGER NOT NULL,
  Matiere_Nom CHAR(64) NOT NULL,
  Constraint Matiere_PK PRIMARY KEY(Matiere_Id)) ;
 
CREATE TABLE Niveau (
  Niveau_Id INTEGER NOT NULL,
  Niveau_Nom CHAR(64) NOT NULL,
  Constraint Niveau_PK PRIMARY KEY(Niveau_Id)) ;

Ainsi qu’une table Matiere_Niveau définie au moyen de l’instruction suivante et qui soit en relation avec la table Matiere d’une part et la table Niveau d’autre part :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE Matiere_Niveau (
  Matiere_Id INTEGER NOT NULL,
  Niveau_Id INTEGER NOT NULL,
  Annee_Appli INTEGER NOT NULL,
  Constraint Matiere_Niveau_PK PRIMARY KEY(Matiere_Id, Niveau_Id),
  Constraint Matiere_Niveau_Matiere_FK FOREIGN KEY (Matiere_Id)
    REFERENCES Matiere(Matiere_Id),
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  Constraint Matiere_Niveau_Niveau_FK FOREIGN KEY (Niveau_Id)
    REFERENCES Niveau(Niveau_Id)
      ON DELETE CASCADE
      ON UPDATE CASCADE) ;

Outre une table Matiere_Niveau_Histo définie au moyen de l’instruction suivante et qui soit en relation avec la table Matiere_Niveau :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE Matiere_Niveau_Histo (
  Mat_Id INTEGER NOT NULL,
  Niv_Id INTEGER NOT NULL,
  Histo_Id INTEGER NOT NULL,
  Date_Debut Datetime Not Null,
  Date_Fin Datetime Not Null,
  Constraint Matiere_Niveau_Histo_PK PRIMARY KEY(Mat_Id, Niv_Id, Histo_Id),
  Constraint Matiere_Niveau_Histo_Matiere_Niveau_FK FOREIGN KEY (Mat_Id, Niv_Id)
    REFERENCES Matiere_Niveau(Matiere_Id, Niveau_Id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

Si l’on interroge la vue INFORMATION_SCHEMA.TABLE_CONSTRAINTS aimablement fournie par le SGBD, on connaît le nom et la nature de chaque contrainte :

Code:

1
2
select  CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ;

Code:

1
2
3
4
5
6
7
8
9
CONSTRAINT_NAME                          TABLE_NAME              CONSTRAINT_TYPE
--------------------------------------   --------------------    ---------------
Matiere_PK                               Matiere                 PRIMARY KEY
Niveau_PK                                Niveau                  PRIMARY KEY
Matiere_Niveau_PK                        Matiere_Niveau          PRIMARY KEY
Matiere_Niveau_Matiere_FK                Matiere_Niveau          FOREIGN KEY
Matiere_Niveau_Niveau_FK                 Matiere_Niveau          FOREIGN KEY
Matiere_Niveau_Histo_PK                  Matiere_Niveau_Histo    PRIMARY KEY
Matiere_Niveau_Histo_Matiere_Niveau_FK   Matiere_Niveau_Histo    FOREIGN KEY

Si l’on interroge la vue INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, on connaît pour chaque contrainte de clé étrangère la contrainte de clé primaire (ou alternative) cible :

Code:

1
2
3
select  CONSTRAINT_NAME AS Cle_etrangere 
      , UNIQUE_CONSTRAINT_NAME AS Cle_referencee   
from    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ;

Code:

1
2
3
4
5
Cle_etrangere                             Cle_referencee
--------------------------------------    --------------
Matiere_Niveau_Matiere_FK                 Matiere_PK
Matiere_Niveau_Niveau_FK                  Niveau_PK
Matiere_Niveau_Histo_Matiere_Niveau_FK    Matiere_Niveau_PK

Pour chaque contrainte, pour connaître chaque colonne et sa position dans la clé (primaire ou étrangère), on consulte la vue INFORMATION_SCHEMA.KEY_COLUMN_USAGE :

Code:

1
2
3
select TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, ORDINAL_POSITION
from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
order by TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION ;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
TABLE_NAME             CONSTRAINT_NAME                          COLUMN_NAME  ORDINAL_POSITION
--------------------   --------------------------------------   -----------  ----------------
Matiere                Matiere_PK                               Matiere_Id   1
Matiere_Niveau         Matiere_Niveau_Matiere_FK                Matiere_Id   1
Matiere_Niveau         Matiere_Niveau_Niveau_FK                 Niveau_Id    1
Matiere_Niveau         Matiere_Niveau_PK                        Matiere_Id   1
Matiere_Niveau         Matiere_Niveau_PK                        Niveau_Id    2
Matiere_Niveau_Histo   Matiere_Niveau_Histo_Matiere_Niveau_FK   Mat_Id       1
Matiere_Niveau_Histo   Matiere_Niveau_Histo_Matiere_Niveau_FK   Niv_Id       2
Matiere_Niveau_Histo   Matiere_Niveau_Histo_PK                  Mat_Id       1
Matiere_Niveau_Histo   Matiere_Niveau_Histo_PK                  Niv_Id       2
Matiere_Niveau_Histo   Matiere_Niveau_Histo_PK                  Histo_Id     3
Niveau                 Niveau_PK                                Niveau_Id    1

Pour connaître la composition de chaque couple clé étrangère/clé primaire (ou alternative), on peut mettre en œuvre une vue ressemblant à quelque chose comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create view Relations as
select  x.TABLE_NAME AS Table_referencante 
      , t.COLUMN_NAME as Colonne_referencante, t.ORDINAL_POSITION as Position
      , z.TABLE_NAME As Table_Referencee, z.CONSTRAINT_NAME AS Cle_referencee
      , u.COLUMN_NAME as Colonne_referencee
from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS x
        INNER JOIN
        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS y
             ON x.CONSTRAINT_NAME = y.CONSTRAINT_NAME
        INNER JOIN
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS z
             ON y.UNIQUE_CONSTRAINT_NAME = z.CONSTRAINT_NAME
        INNER JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE t
             ON x.CONSTRAINT_NAME = t.CONSTRAINT_NAME
       INNER JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
             ON y.UNIQUE_CONSTRAINT_NAME = u.CONSTRAINT_NAME
             and u.ORDINAL_POSITION = t.ORDINAL_POSITION ;

Pour présenter les couples table référençante / table référencée, avec la correspondance des colonnes :

Code:

1
2
3
4
Select  Table_referencante, Colonne_referencante
      , Table_Referencee, Colonne_referencee
From    Relations
order by Table_referencante, Position ;

Code:

1
2
3
4
5
6
Table_referencante    Colonne_referencante  Table_Referencee  Colonne_referencee
--------------------  --------------------  ----------------  ------------------
Matiere_Niveau        Matiere_Id            Matiere           Matiere_Id
Matiere_Niveau        Niveau_Id             Niveau            Niveau_Id
Matiere_Niveau_Histo  Mat_Id                Matiere_Niveau    Matiere_Id
Matiere_Niveau_Histo  Niv_Id                Matiere_Niveau    Niveau_Id

Si donc les noms de tables passés en paramètres sont, par exemple, dans l’ordre (Matiere, Matiere_Niveau), on voit assez facilement quelle(s) requête(s) développer pour conclure que c’est Matiere_Niveau qui référence Matiere, et non l’inverse. On pourra aussi conclure qu'il n’y a aucun lien direct entre les tables Matiere et Niveau.

A vous de jouer.