contrainte d'integrité dans sql

Version imprimable

04/10/2006, 09h52
nadia_ept

contrainte d'integrité dans sql

Bonjour,
pour implémenter mon modèle relationnel en sql j'ai besoin d'une contrainte d'integrité qui conditionne un attribut dans une entité par un autre attribu se trouvant dans une autre entité (est ce que c'est la commande CHECK dans ce cas et comment est la syntaxe?)

merci
04/10/2006, 10h27
sygale
Je pense que tu parle de clé étrangère en fait :

Fait une recherche sur FOREIGN KEY
Code:

1 2 3 4 5 ALTER TABLE T1ADD ( CONSTRAINT FK1 FOREIGN KEY (CH1) REFERENCES T2 (CH34)) /
Les contraintes check sont plus "orientée" vérification de données. Tel que les valeurs de ce champs doite être comprise entre 1 et 150.

Le systeme PRIMARY KEY / FOREIGN KEY répondra a tes besoins.

Imaginons une table EMPLOYE
emp_id
nom
dept_id

Imaginons une table DEPT (service)
dept_id
nom

Tu veux que l'ID de chaque employés et de chaque services soient unique.
--> Tu créé une contrainte de clé primaire (PRIMARY KEY). Une sur le emp_id de la table EMPLOYE, et une sur dept_id de la table DEPT.

Un employé est rattaché a un service. Dans la table EMPLOYE tu remarques que la colone dept_id est présente, tout comme dans la table DEPT. Pour t'assurer donc que tes employés sont bien rattaché a un service qui existe deja dans la table DEPT
--> Tu créé une contrainte de clé étrangeère (FOREIGN KEY)

A ce momont la, tes insertions de valeurs dans la colonne dept_id de la table EMPLOYE, seront conditionées par les valeurs de DEPT.dept_id

En SQL mon exemple s'illustre comme ceci :
Code:

1 2 3 4 5 6 7 8 9 create table EMPLOYE (emp_id number(3), nom varchar2(10), dept_id number(2)); create table DEPT (dept_id number(2), nom varchar2(10)); alter table EMPLOYE add constraint "PK_EMP_ID" PRIMARY KEY (emp_id); alter table DEPT add constraint "PK_DEPT_ID" PRIMARY KEY (dept_id); alter table EMPLOYE add constraint "FK_DEPT_ID" FOREIGN KEY (dept_id) REFERENCES DEPT(dept_id);

05/10/2006, 07h14
nadia_ept

Merci
En fait j'ai une table Match qui contient le nombre de spectateur (nbspectateurs) et une table Stade qui contient le nombre de places dans ce stade (nbPlaces). la contrainte que je dois mettre dans l'entité Match est que nbspectateurs < nbPlaces.
et aussi j'ai deja une clé primaire qui lie l'entité Match par l'entité stade (puisque c'est une relation simlpe (1,1))
05/10/2006, 08h28
pifor

Dans ce cas, il faut créer un trigger car il n'existe pas de contrainte d'intégrité "simple" qui peut réaliser ce contrôle. Il y a peut-être une solution compliquée avec une vue matérialisée mais le trigger est sans doute la solution la plus simple.
05/10/2006, 11h30
sygale

Citation:

Envoyé par pifor

Il y a peut-être une solution compliquée avec une vue matérialisée

Je suis d'accord pour le trigger mais comment faire un contrainte calculée avec un vue matérialisée ?

L'idée est de créer une vue matérialisée qui représente une vue des données à vérifier et de créer la contrainte sur la vue matérialisée.

Exemple:

Code:

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
SQL> 
SQL> drop table tmatch;
 
Table dropped.
 
SQL> drop table tstade;
 
Table dropped.
 
SQL> drop materialized view mv;
 
Materialized view dropped.
 
SQL> 
SQL> 
SQL> whenever sqlerror exit failure;
SQL> 
SQL> create table  tstade(nst number primary key, npl number);
 
Table created.
 
SQL> create table  tmatch(nm number primary key, nst number references tstade, nsp number);
 
Table created.
 
SQL> 
SQL> create materialized view log on tmatch
  2  with primary key, rowid
  3  including new values;
 
Materialized view log created.
 
SQL> 
SQL> create materialized view log on tstade
  2  with primary key, rowid
  3  including new values;
 
Materialized view log created.
 
SQL> 
SQL> create materialized view mv
  2  refresh force on commit
  3  as
  4  select tmatch.nm, tmatch.nst, tmatch.nsp, tstade.npl
  5  from tmatch, tstade
  6  where tmatch.nst = tstade.nst;
 
Materialized view created.
 
SQL> 
SQL> alter table mv add constraint c_stade_trop_petit check (nsp <= npl);
 
Table altered.
 
SQL> 
SQL> 
SQL> insert into tstade values (1, 100);
 
1 row created.
 
SQL> insert into tmatch values (1, 1, 99);
 
1 row created.
 
SQL> insert into tmatch values (2, 1, 100);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into tmatch values (3, 1, 101);
 
1 row created.
 
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path 
ORA-02290: check constraint (TEST.C_STADE_TROP_PETIT) violated

:ccool: trop bien
Mais je comprends la VM match/stade + contrainte

Citation:

Envoyé par pifor

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> create materialized view mv
  2  refresh force on commit
  3  as
  4  select tmatch.nm, tmatch.nst, tmatch.nsp, tstade.npl
  5  from tmatch, tstade
  6  where tmatch.nst = tstade.nst;
 
Materialized view created.
 
SQL> 
SQL> alter table mv add constraint c_stade_trop_petit check (nsp <= npl);
 
Table altered.

Par contre à quoi te sert les view log ? :oops:

Citation:

Envoyé par pifor

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL> create materialized view log on tmatch
  2  with primary key, rowid
  3  including new values;
 
Materialized view log created.
 
SQL> 
SQL> create materialized view log on tstade
  2  with primary key, rowid
  3  including new values;
 
Materialized view log created.

05/10/2006, 18h12
pifor

Citation:

Par contre à quoi te sert les view log ?

D'après la doc http://download-uk.oracle.com/docs/c...0/mv.htm#49055

Citation:

Materialized view logs are required if you want to use fast refresh

Non, je ne suis pas un spécialiste des vues matérialisées ;)
05/10/2006, 19h35
remi4444

Citation:

Envoyé par sygale

Par contre à quoi te sert les view log ? :oops:

à Rien! :D

Les journaux servent à faire de la réplication différentielle (FAST) mais ce ne sera pas possible pour ce genre de définition de vue matérialisée. Le fait de demander du FORCE masque le problème parceque sa définition c'est "si tu peux faire du FAST, fait le mais sinon fait du COMPLET) et en l'occurence, ici il n'y aura que du complet possible, donc les journaux de vue matérialisée ne serviront à rien...

En tout cas faire un RAISE_APPLICATION_ERROR dans un trigger me semble aussi beaucoup plus sûr et beaucoup plus adapté au problème!
06/10/2006, 10h44
sygale

Merci pour toutes ces explications :P

Oui nous sommes tous d'accord avec le trigger,
cependant la solution avec VM m'étant inconnue, j'ai demandé des explcations ce que pifor m'a donné bien gentillement

Merci
06/10/2006, 11h37
remi4444

Au fait je crois qu'il faut faire un trigger "AFTER STATEMENT" sinon il y aura un problème lors du comptage de la table en "mutation".....
06/10/2006, 11h46
sygale

un trigger before insert, update avec une petite vérifaction nbSpectateurs du match < nb place du stade devrait suffir non ?

L'erreur de table en mutation c'est quand tu tentes un calcul sur la propre table ou se situe le trigger et qui est donc en cours de modification.
06/10/2006, 13h22
remi4444

:oops:

oui désolé, j'avais pas suivi, je croyait qu'ils falait faire un comptage...