Quel ordre utiliser pour les contraintes sur les tables ?

**Icewall** · 18/04/2008, 21h02

Je suis un nouveau venu !!!

Je débute en SQL mais j'arrive à créer des requêtes imbriquées et des jointures. Je réalisais mes requêtes sur des tables déjà créées (données insérées d'avance).

J'ai installé un serveur à domicile et je pensais pouvoir recréer mes tables facilement...

Mais visiblement, il faut exécuter les contraintes d'intégrité référentiel (PK, FK,...) dans un certain ordre. Sinon, il y a plein de message d'erreurs quand les données sont insérées dans les différentes tables.

Je n'ai que 5 tables (la table Grade est non reliée aux autres) :
Professor
Course
Section
Student
Grade

3 section_ID
2 professor_ID
2 course_ID
1 delegate_ID
1 student_ID

Quelqu'un peut-il m'expliquer dans quel ordre faut-il exécuter les contraintes ?
Quel est la logique à suivre ?

**draco62** · 18/04/2008, 21h08

Salut,

Il faut que tu commences par créer les constraintes de PK des tables qui n'ont pas de FK.

Ensuite du crées les contraintes des autres tables.

A savoir que pour créer une contrainte FK, il faut que la contrainte PK existe dans la table liée.

Si tu n'y arrive pas, dis-le.

Bon courage

**Icewall** · 18/04/2008, 22h04

Je n'ai aucun message d'erreurs pour mes 5 CREATE TABLE.

Ensuite j'ai un autre fichier SQL pour les INSERT INTO ... VALUES ...
C'est à ce moment que les messages d'erreurs apparaissent

J'ai fait un View Dependencies sur chaque table et voilà ce que ça donne :

Table Course
course Primary Key

Table Grade
grade Primary Key

Table Professor
- professor Primary Key
___course Foreign Key

Table Section
- section Primary Key
___ - professor Foreign Key
________ course Foreign Key
_____student Foreign Key

Table student
student Primary Key

J'ai rajouté dans les relations les PK et les FK (message réédité)

**Alain Defrance** · 19/04/2008, 04h03

et bienvenue,

Commence par nous montrer ton SQL et ton erreur, sinon c'est un peu difficile de trouver d'où viens le problème :/

**fsmrel** · 19/04/2008, 12h32

Une technique consiste à exécuter dans un 1er temps les instructions CREATE TABLE, sans définir les contraintes d'intégrité référentielle.

Quand tout est en place, dans un 2e temps, vous définissez les contraintes référentielles par ALTER TABLE.

Dans un 3e temps vous pouvez procéder aux inserts, mais dans un ordre qui tienne compte des contraintes. Par exemple, pour effectuer un insert dans la table Section, impliquant l’élève Toto, il aura d’abord fallu insérer la ligne qui va bien dans la table Student, avec une valeur de clé primaire pour Toto, qui sera la valeur de clé étrangère correspondante dans Section. A défaut, vous cherchez à insérer des orphelins, ce qui est interdit du fait de l’intégrité référentielle.

**Icewall** · 19/04/2008, 22h56

1er Code de création de table et des contraintes

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
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
CREATE TABLE section (
  section_id int NOT NULL,
  section_name varchar(50),
  delegate_id int
  CONSTRAINT PK_section PRIMARY KEY (section_id)
)
 
CREATE TABLE professor (
  professor_id int NOT NULL,
  professor_name varchar(30) NOT NULL,
  professor_surname varchar(30) NOT NULL,
  section_id int NOT NULL,
  professor_office int NOT NULL,
  professor_email varchar(30) NOT NULL,
  professor_hire_date datetime NOT NULL,
  professor_wage int NOT NULL,
  CONSTRAINT PK_professor PRIMARY KEY (professor_id),
  constraint FK_professor_section foreign key (section_id) references section (section_id)
)
 
CREATE TABLE course (
  course_id varchar(8) NOT NULL ,
  course_name varchar(200) NOT NULL ,
  course_ects decimal(3,1) NOT NULL,
  professor_id int NOT NULL,
  CONSTRAINT PK_course PRIMARY KEY (course_id),
  constraint FK_course_professor foreign key (professor_id) references professor (professor_id)
)
 
CREATE TABLE student (
  student_id int NOT NULL,
  first_name varchar(50),
  last_name varchar(50),
  birth_date datetime,
  login varchar(50),
  section_id int,
  year_result int,
  course_id varchar(6) NOT NULL,
  CONSTRAINT PK_student PRIMARY KEY (student_id),
  constraint FK_student_section foreign key (section_id) references section (section_id)
)
 
CREATE TABLE grade (
  grade char(2) NOT NULL ,
  lower_bound int NOT NULL,
  upper_bound int NOT NULL,
  CONSTRAINT PK_grade PRIMARY KEY (grade)
)

2ème code d'insertion des données

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
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
Begin transaction
use DBSlide
delete from section
delete from professor
delete from course
delete from student
delete from grade
 
INSERT INTO section VALUES (1010, 'BSc Management', 12);
INSERT INTO section VALUES (1020, 'MSc Management', 9);
INSERT INTO section VALUES (1110, 'BSc Economics', 15);
INSERT INTO section VALUES (1120, 'MSc Economics', 6);
INSERT INTO section VALUES (1310, 'BA Sociology', 23);
INSERT INTO section VALUES (1320, 'MA Sociology', 6);
 
INSERT INTO professor VALUES (1, 'zidda', 'pietro', 1020, 402, 'pzidda', '2004-12-11', 1900);
INSERT INTO professor VALUES (2, 'decrop', 'alain', 1120, 403, 'adecrop', '2003-05-09', 1950);
INSERT INTO professor VALUES (3, 'giot', 'pierre', 1310, 404, 'pgiot', '2002-12-21', 2100);
INSERT INTO professor VALUES (4, 'lecourt', 'christelle', 1310, 406, 'clecourt', '2003-05-07', 1750);
INSERT INTO professor VALUES (5, 'scheppens', 'georges', 1020, 410, 'gscheppens', '1986-10-09', 2450);
INSERT INTO professor VALUES (6, 'louveaux', 'francois', 1110, 407, 'flouveaux', '1990-05-07', 2200);
 
INSERT INTO course VALUES ('EING2234', 'Derivatives', 3.0, 3);
INSERT INTO course VALUES ('ECGE2184', 'Marketing management', 3.5, 2);
INSERT INTO course VALUES ('EING2283', 'Marketing engineering', 4.0, 1);
INSERT INTO course VALUES ('ECGE2183', 'Financial Management', 4.0, 3);
INSERT INTO course VALUES ('EING2383', 'Supply chain management et e-business', 2.5, 5);
 
INSERT INTO student VALUES (1, 'Georges', 'Lucas', '1944-05-17 00:00:00', 'glucas', 1320, 10, 'EG2210');
INSERT INTO student VALUES (2, 'Clint', 'Eastwood', '1930-05-31 00:00:00', 'ceastwoo', 1010, 4, 'EG2210');
INSERT INTO student VALUES (3, 'Sean', 'Connery', '1930-08-25 00:00:00', 'sconnery', 1020, 12, 'EG2110');
INSERT INTO student VALUES (4, 'Robert', 'De Niro', '1943-08-17 00:00:00', 'rde niro', 1110, 3, 'EG2210');
INSERT INTO student VALUES (5, 'Kevin', 'Bacon', '1958-07-08 00:00:00', 'kbacon', 1120, 16, '0');
INSERT INTO student VALUES (6, 'Kim', 'Basinger', '1953-12-08 00:00:00', 'kbasinge', 1310, 19, '0');
INSERT INTO student VALUES (7, 'Johnny', 'Depp', '1963-06-09 00:00:00', 'jdepp', 1110, 11, 'EG2210');
INSERT INTO student VALUES (8, 'Julia', 'Roberts', '1967-10-28 00:00:00', 'jroberts', 1120, 17, '0');
INSERT INTO student VALUES (9, 'Natalie', 'Portman', '1981-06-09 00:00:00', 'nportman', 1010, 4, 'EG2210');
INSERT INTO student VALUES (10, 'Georges', 'Clooney', '1961-05-06 00:00:00', 'gclooney', 1020, 4, 'EG2110');
INSERT INTO student VALUES (11, 'Andy', 'Garcia', '1956-04-12 00:00:00', 'agarcia', 1110, 19, '0');
INSERT INTO student VALUES (12, 'Bruce', 'Willis', '1955-03-19 00:00:00', 'bwillis', 1010, 6, 'EG2210');
INSERT INTO student VALUES (13, 'Tom', 'Cruise', '1962-07-03 00:00:00', 'tcruise', 1020, 4, 'EG2110');
INSERT INTO student VALUES (14, 'Reese', 'Witherspoon', '1976-03-22 00:00:00', 'rwithers', 1020, 7, 'EG1020');
INSERT INTO student VALUES (15, 'Sophie', 'Marceau', '1966-11-17 00:00:00', 'smarceau', 1110, 6, '0');
INSERT INTO student VALUES (16, 'Sarah', 'Michelle Gellar', '1977-04-14 00:00:00', 'smichell', 1020, 7, 'EG2110');
INSERT INTO student VALUES (17, 'Alyssa', 'Milano', '1972-12-19 00:00:00', 'amilano', 1110, 7, '0');
INSERT INTO student VALUES (18, 'Jennifer', 'Garner', '1972-04-17 00:00:00', 'jgarner', 1120, 18, '0');
INSERT INTO student VALUES (19, 'Michael J.', 'Fox', '1969-06-20 00:00:00', 'mfox', 1310, 3, '0');
INSERT INTO student VALUES (20, 'Tom', 'Hanks', '1956-07-09 00:00:00', 'thanks', 1020, 8, 'EG2110');
INSERT INTO student VALUES (21, 'David', 'Morse', '1953-10-11 00:00:00', 'dmorse', 1110, 2, '0');
INSERT INTO student VALUES (22, 'Sandra', 'Bullock', '1964-07-26 00:00:00', 'sbullock', 1010, 2, 'EG1020');
INSERT INTO student VALUES (23, 'Keanu', 'Reeves', '1964-09-02 00:00:00', 'kreeves', 1020, 10, 'EG2210');
INSERT INTO student VALUES (24, 'Shannen', 'Doherty', '1971-04-12 00:00:00', 'sdoherty', 1320, 2, 'EG2120');
INSERT INTO student VALUES (25, 'Halle', 'Berry', '1966-08-14 00:00:00', 'hberry', 1320, 18, 'EG2210');
 
INSERT INTO grade VALUES ('IG', 0, 7);
INSERT INTO grade VALUES ('I', 8, 9);
INSERT INTO grade VALUES ('F', 10, 11);
INSERT INTO grade VALUES ('S', 12, 13);
INSERT INTO grade VALUES ('B', 14, 15);
INSERT INTO grade VALUES ('TB', 16, 17);
INSERT INTO grade VALUES ('E', 18, 20);
 
commit

Le 2ème code ne fonctionne pas

Visiblement les INSERT ne sont pas dans le bon ordre.

Ainsi que plusieurs fois le même message d'erreur :
La conversion d'un type de données CHAR en type DATETIME a donné une valeur hors limite de date et d'heure.

**Alain Defrance** · 20/04/2008, 00h17

J'ai bien regarder en fonction du MLD que tu nous a passé, et je ne trouves pas l'erreur, l'ordre a l'air bon...

**fsmrel** · 20/04/2008, 01h53

J'ai fait un test avec SQL Server 2005 en remplaçant le type DateTime par Char : ça marche.

Par contre, si on remet

professor_hire_date datetime NOT NULL,

à la place de

professor_hire_date char(10) NOT NULL,

on a droit à :

L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY 'FK_course_professor' (table professor)

SQL Server serait-il fantasque ? Qu'il rouspète quant au format de la date, pourquoi pas, mais là...

**fsmrel** · 20/04/2008, 02h13

J'ai regardé quel Insert était rejeté. C'est le suivant :

INSERT INTO professor VALUES (3, 'giot', 'pierre', 1310, 404, 'pgiot', '2002-12-21', 2100);

Et si vous permutez le jour et l'année, tout se passe bien...

INSERT INTO professor VALUES (3, 'giot', 'pierre', 1310, 404, 'pgiot', '2002-21-12', 2100);

Encore une histoire de format de date anglo-saxon ? Mais quel rapport avec les clés étrangères...

Bref, de fins connaisseurs vous expliqueront tout...

**TryExceptEnd** · 20/04/2008, 11h34

Envoyé par Icewall

Ainsi que plusieurs fois le même message d'erreur :
La conversion d'un type de données CHAR en type DATETIME a donné une valeur hors limite de date et d'heure.

Et si tu écrivait les dates sous le format normal "JJ-MM-AAAA", ça ne serait pas mieux ?

**fsmrel** · 20/04/2008, 12h16

Envoyé par TryExceptEnd

Et si tu écrivait les dates sous le format normal "JJ-MM-AAAA", ça ne serait pas mieux ?

"AAAA-MM-JJ" (alias "YYYY-MM-DD"), c'est quand même le format ISO me semble-t-il...

Après vérification, la date au format ISO 8601 doit être présentée sous cette forme :

yyyy-mm-ddThh:mm:ss[.mmm]

C'est-à-dire concernant M. Giot :

'2002-12-21T00:00:00'

L'ajout de "T" vaut pour tout le monde, sinon attention aux surprises !

Cf. http://msdn2.microsoft.com/en-us/library/ms190977.aspx

**TryExceptEnd** · 20/04/2008, 13h04

SQL Server reconnaît les données de date et d'heure entre des guillemets simples (') dans les formats suivants :

date alphabétique, telle que '15 avril 1998' ;
formats de date numérique, tels que '5/4/1998' ;
formats de chaîne non séparée. Par exemple, ''07121998' pour 7 décembre 1998.

in http://msdn2.microsoft.com/fr-fr/library/ms180878.aspx

**fsmrel** · 20/04/2008, 13h27

formats de date numérique, tels que '5/4/1998'

C'est quoi ? Le 5 avril ou le 4 mai ?

Bref, SQL Server et les dates, ça rappelle les écuries d'Augias. Le mieux est de préciser au départ le format que l'on attend, par exemple :

SET DATEFORMAT ymd

Et cette fois-ci, l'insert

INSERT INTO professor VALUES (3, 'giot', 'pierre', 1310, 404, 'pgiot','2002-12-21' , 2100);

dit que la date affectée à M. Giot est le 21 décembre.

(Sous réserve d'autres surprises façon Augias...)

Sinon, le '2002-21-12' provoque à nouveau le message

L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY

Intéressant...

**TryExceptEnd** · 20/04/2008, 13h47

Envoyé par fsmrel

C'est quoi ? Le 5 avril ou le 4 mai ?

Aucune idée, faut poser la question a microsoft,
moi je travaille avec Firebird et les dates sont au format 'AAAA/MM/JJ' ou 'AAAA-MM-JJ' ou encore 'AAAA MM JJ'.

**Icewall** · 20/04/2008, 19h52

Merci à fsmrel, TryExceptEnd, kazou et draco62.

J'ai suivi vos conseils concernant :

L'ordre des insertions fonction des clés (intégrité référentielle)
La modification de professor_hire_date char(10) NOT NULL,
Le T manquant au milieu des insertions de dates.

Je n'imaginais pas que de simples dates pouvaient créer un message d'erreur avec une foreign key...
Je ferai plus attention aux formats des dates la prochaine fois.

Je vais pouvoir m'amuser avec des requêtes maintenant que la structure de base est créée

Quel ordre utiliser pour les contraintes sur les tables ?

Langage SQL

Discussions similaires

Partager

Partager