Foreign keys apprendre

**ptiteuf** · 01/06/2009, 15h42

Bonjour a tous,

voila je débute complètement en MYSQL et j'ai besoin de savoir si je vais pas une erreur.

je suis occuper a essaye de faire une base avec les code postaux + ville + pays et ce en 6 langues

voici mon code sql.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

-- -----------------------------------------------------
-- Table `pays`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pays` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `pays` (
`idpays` INT NOT NULL AUTO_INCREMENT ,
`paysfr` VARCHAR(100) NOT NULL ,
`paysen` VARCHAR(100) NOT NULL ,
`paysit` VARCHAR(100) NOT NULL ,
`paysde` VARCHAR(100) NOT NULL ,
`paysnl` VARCHAR(100) NOT NULL ,
`payses` VARCHAR(100) NOT NULL ,
`payspt` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`idpays`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `ville`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ville` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `ville` (
`idville` INT NOT NULL AUTO_INCREMENT ,
`villefr` VARCHAR(150) NOT NULL ,
`paysfr` VARCHAR(100) NOT NULL ,
`villeen` VARCHAR(150) NOT NULL ,
`paysen` VARCHAR(100) NOT NULL ,
`villeit` VARCHAR(150) NOT NULL ,
`paysit` VARCHAR(100) NOT NULL ,
`villede` VARCHAR(150) NOT NULL ,
`paysde` VARCHAR(100) NOT NULL ,
`villenl` VARCHAR(150) NOT NULL ,
`paysnl` VARCHAR(100) NOT NULL ,
`villees` VARCHAR(150) NOT NULL ,
`payses` VARCHAR(100) NOT NULL ,
`villept` VARCHAR(150) NOT NULL ,
`payspt` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`idville`) ,
CONSTRAINT `paysfr`
FOREIGN KEY (`paysfr` )
REFERENCES `mydb`.`pays` (`paysfr` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `paysen`
FOREIGN KEY (`paysen` )
REFERENCES `mydb`.`pays` (`paysen` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `paysit`
FOREIGN KEY (`paysit` )
REFERENCES `mydb`.`pays` (`paysit` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `paysde`
FOREIGN KEY (`paysde` )
REFERENCES `mydb`.`pays` (`paysde` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `paysnl`
FOREIGN KEY (`paysnl` )
REFERENCES `mydb`.`pays` (`paysnl` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `payses`
FOREIGN KEY (`payses` )
REFERENCES `mydb`.`pays` (`payses` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `payspt`
FOREIGN KEY (`payspt` )
REFERENCES `mydb`.`pays` (`payspt` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

SHOW WARNINGS;
CREATE INDEX `paysfr` ON `ville` (`paysfr` ASC) ;

SHOW WARNINGS;
CREATE INDEX `paysen` ON `ville` (`paysen` ASC) ;

SHOW WARNINGS;
CREATE INDEX `paysit` ON `ville` (`paysit` ASC) ;

SHOW WARNINGS;
CREATE INDEX `paysde` ON `ville` (`paysde` ASC) ;

SHOW WARNINGS;
CREATE INDEX `paysnl` ON `ville` (`paysnl` ASC) ;

SHOW WARNINGS;
CREATE INDEX `payses` ON `ville` (`payses` ASC) ;

SHOW WARNINGS;
CREATE INDEX `payspt` ON `ville` (`payspt` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `cpostaux`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `cpostaux` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cpostaux` (
`idcpostaux` INT NOT NULL AUTO_INCREMENT ,
`cp` VARCHAR(15) NOT NULL ,
`villefr` VARCHAR(150) NOT NULL ,
`villeen` VARCHAR(150) NOT NULL ,
`villeit` VARCHAR(150) NOT NULL ,
`villede` VARCHAR(150) NOT NULL ,
`villenl` VARCHAR(150) NOT NULL ,
`villees` VARCHAR(150) NOT NULL ,
`villept` VARCHAR(150) NOT NULL ,
PRIMARY KEY (`idcpostaux`) ,
CONSTRAINT `villefr`
FOREIGN KEY (`villefr` )
REFERENCES `mydb`.`ville` (`villefr` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `villeen`
FOREIGN KEY (`villeen` )
REFERENCES `mydb`.`ville` (`villeen` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `villeit`
FOREIGN KEY (`villeit` )
REFERENCES `mydb`.`ville` (`villeit` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `villede`
FOREIGN KEY (`villede` )
REFERENCES `mydb`.`ville` (`villede` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `villenl`
FOREIGN KEY (`villenl` )
REFERENCES `mydb`.`ville` (`villenl` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `villees`
FOREIGN KEY (`villees` )
REFERENCES `mydb`.`ville` (`villees` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `villept`
FOREIGN KEY (`villept` )
REFERENCES `mydb`.`ville` (`villefr` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

SHOW WARNINGS;
CREATE INDEX `villefr` ON `cpostaux` (`villefr` ASC) ;

SHOW WARNINGS;
CREATE INDEX `villeen` ON `cpostaux` (`villeen` ASC) ;

SHOW WARNINGS;
CREATE INDEX `villeit` ON `cpostaux` (`villeit` ASC) ;

SHOW WARNINGS;
CREATE INDEX `villede` ON `cpostaux` (`villede` ASC) ;

SHOW WARNINGS;
CREATE INDEX `villenl` ON `cpostaux` (`villenl` ASC) ;

SHOW WARNINGS;
CREATE INDEX `villees` ON `cpostaux` (`villees` ASC) ;

SHOW WARNINGS;
CREATE INDEX `villept` ON `cpostaux` (`villept` ASC) ;

SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Merci d'avance pour vos réponse et conseil.

**Antoun** · 01/06/2009, 17h53

Ta modélisation part sur les mauvais principes. Tes entités de base sont la ville et le pays. Le code postal est un attribut de la ville. Les noms de pays et de villes sont des attributs des entités correspondantes ; mais plutôt que de créer six colonnes à chaque fois, il est plus intéressant et plus simple de considérer que tu as une entité Langue. Du coup, les noms deviennent des associations entre Pays et Langue d'une part, Ville et Langue d'autre part.

Plus concrètement, ça donne donc :

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
create table Pays (
  IDpays int primary key
  ) ;
create table Villes (IDville int primary key, 
  codepostal varchar(10), 
  IDpays int references pays (IDpays)
  ) ;
create table Langues(
  IDlangue int primary key, 
  langue varchar(20)
  ) ;
create table NomsPays(
  IDpays int references Pays(IDpays),
  IDlangue int references Langues(IDlangue),
  NomPays varchar(100) not null,
  primary key (IDpays, IDlangue) 
) ;
create table NomsVilles(
  IDville int references Villes(IDville),
  IDlangue int references Langues(IDlangue),
  NomVille varchar(150) not null,
  primary key (IDville, IDlangue) 
) ;

**ptiteuf** · 01/06/2009, 19h07

Merci pour ta réponse et je vais de ce pas l'installer sur ma base et la travailler.

**Xunil** · 01/06/2009, 19h14

Un code postal peut différer pour les grandes villes où il y a des arrondissements, comme paris et marseille, je crois qu'il faut prendre en compte ce paramètre.

**ptiteuf** · 01/06/2009, 19h27

Envoyé par Xunil

Un code postal peut différer pour les grandes villes où il y a des arrondissements, comme paris et marseille, je crois qu'il faut prendre en compte ce paramètre.

tu sais m'éclairer sur cela et me dire si je dois modifier les tables de notre modérateur.

**Antoun** · 01/06/2009, 22h05

Envoyé par Xunil

Un code postal peut différer pour les grandes villes où il y a des arrondissements, comme paris et marseille, je crois qu'il faut prendre en compte ce paramètre.

Et réciproquement, plusieurs (petites) villes peuvent correspondre au même code postal. Donc si on veut être très formel, ça donne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE Villes (
  IDville int PRIMARY KEY, 
  IDpays int REFERENCES pays (IDpays)
  ) ;
CREATE TABLE CodesPostaux (
  CodePostal varchar(15) PRIMARY KEY
) ;
CREATE TABLE Attribution_CP (
  IDville REFERENCES Villes(IDville),
  CodePostal varchar(15) REFERENCES CodesPostaux(CodePostal),
  PRIMARY KEY (IDville, CodePostal) 
) ;

Le reste ne change pas.

**ptiteuf** · 01/06/2009, 22h42

merci pour votre aide precieuse,

voila j'ai essaye d'installer la modification porter suite au probleme de code postaux et j'ai une erreur.

voici le code sql que j'ai essaye de mettre

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
CREATE TABLE Pays (
  IDpays int PRIMARY KEY
) ;
 
CREATE TABLE Langues(
  IDlangue int PRIMARY KEY, 
  langue varchar(20)
) ;
 
CREATE TABLE NomsPays(
  IDpays int REFERENCES Pays(IDpays),
  IDlangue int REFERENCES Langues(IDlangue),
  NomPays varchar(100) NOT NULL,
  PRIMARY KEY (IDpays, IDlangue) 
) ;
 
CREATE TABLE NomsVilles(
  IDville int REFERENCES Villes(IDville),
  IDlangue int REFERENCES Langues(IDlangue),
  NomVille varchar(150) NOT NULL,
  PRIMARY KEY (IDville, IDlangue) 
) ;
 
CREATE TABLE Villes (
  IDville int PRIMARY KEY, 
  IDpays int REFERENCES pays (IDpays)
) ;
 
CREATE TABLE CodesPostaux (
  CodePostal varchar(15) PRIMARY KEY
) ;
 
CREATE TABLE Attribution_CP (
  IDville REFERENCES Villes(IDville),
  CodePostal varchar(15) REFERENCES CodesPostaux(CodePostal),
  PRIMARY KEY (IDville, CodePostal) 
) ;

et voila le code erreur

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES Villes(IDville),
CodePostal varchar(15) REFERENCES CodesPostaux(Co' at line 2

merci pour votre aide.

bon j'ai corriger un truc qui me semble etais cela le blem voici le sql

CREATE TABLE Attribution_CP (
IDville int REFERENCES Villes(IDville),
CodePostal varchar(15) REFERENCES CodesPostaux(CodePostal),
PRIMARY KEY (IDville, CodePostal)
) ;

j'ai juste ajouter le int a IDville, j'espere que c'etais bien cela.

Foreign keys apprendre

MySQL

Vue hybride

Discussions similaires

Partager

Partager