Les trous dans les clés primaires d'une base de données ? [Résolu]

dymezac · 26/09/2006, 12h35

Bonjour,

lors de la conception des tables d'une base de données sous MySql, nous nous posons la question suivante (valable pour les autres SGBD) :

Devrais t'on utiliser l'option "auto-increment" de la clé primaire ?

en effet, si le type de la clé primaire est int, supposons qu'un grand nombre d'utilisateur crée des enregistrements dans une des tables et qu'au bout d'un moment il y ait des trous dans les clé numériques (exemple simple : l'autoincrement arrive à 10 000 mais il n'y a que 300 enregistrements dans la table). A chaque fois qu'on ajoute un enregistrement l'auto increment prend le dernier numéro soit : 10 001, alors qu'en fait il aurait pu regarder quel était le prochain id disponible à partir de 1

je sais qu'on peut supprimer une colone clé primaire et la recrée pour renuméroter tout comme il faut à partir de 1, mais je pense que ça ne suit pas l'intégrité référentiel (est ce que ça modifie les clés liées, etc..)

qu'en pensez vous ? y a t'il des outils pour cela dans les SGBD ?

merci !

Tofalu · 26/09/2006, 16h01

Les trous sont perdus, et ce n'est pas bien grave

Imagine un processus de facturation que tu purges tout les cinq ans.

Monsieur A a une facture en 2005 n° 2003
En 20010, on purge, le n°2003 est libre, monsieur B le récupère.

Le 10 Juin 2011, le service compta demande de retrouver la facture de Mr A n° 2003 ...

dymezac · 26/09/2006, 16h17

bonjour et merci pour votre réponse..

mais dans le cas ou il n'y a pas besoin de retrouver l'enregistrement.. (pas de serive compta...) .. une table dont on a pas besoin de garder une trace des enregistrements, par exemple une table ou des utilisateurs peuvent ajouter et supprimer des enregistrements comme ils le veulent.

Autre problème : et si au bout de 5 ans on arrive au max de int qui est d'environ 4 milliards d'enregistrement (admettons).. il va bien y avoir un probleme, comme pour le bug de l'an 2000

Enfin, n'existe t'il pas de routine, ou d'outils intégrés à certains SGBDR qui renumérotent bien les clés primaires ainsi que les numéros des clés liés dans les autres tables.. ? ça serait pourtant une solution, et c'est pourquoi je me demande si ça n'existe pas deja... si personne n'a deja réfléchit à ce probleme

n'y a t'il pas de grosses bases de données qui ont atteint le max de int de leur clé primaire par exemple ?

orafrance · 26/09/2006, 16h37

je serais tenté de penser qui tu vas atteindre la limite de capacité physique (coté perf et coût d'exploitation) avant d'atteindre la limite de int... au pire tu met un float... pourquoi absolument un int ?

dymezac · 26/09/2006, 17h15

oui c'est vrai.. autant prendre un float..

c'est vrai pour le problème des perfs je n'avais pas penser.

comment font les gens dans ce cas la justement, quand une base de données atteint le max des perfs.. ? il faut faire du menage dans la base ? .. répartir la base de données sur plusieurs machines ?

en fait donc, par rapport à ma question d'origine, il n'existe pas d'outils qui gère le renumérotage des clés primaires en gérant les clés liées dans les autres tables ??

SQLpro · 26/09/2006, 23h00

Vieux débat...
Question sempiternelle posée 10987765789087654567 fois

Réponse simple:
1) on ne recupère JAMAIS une clef utilisée.
2) à raison d'une insertion par seconde en continue dans la journée, pour épuise toutes les nombres d'un INT il faut... 66 ans.
Imaginons une table des clients avec un auto incrément type INT, il y aura donc dans cette table 4 milliards de clients, soit près de 50% de la population terrestre....

A +

SQLpro · 27/09/2006, 09h02

Et si cela ne suffit pas on peut passer la colonne en BIGINT, ce qui permet de prévoir large.... Pour épuiser ces nombres dans ce conctexte il faut à raison d'une insertion par seconde, quelque centaines de millénaires... D'ici là on aura le temps de voir !

Enfin, l'utilisation du FLOAT est stupide ! En effet on peut se trouver dans le cas ou il est impossible de retrouver la clef !!!

Comment-est-ce possible ???

Simplement parce que l'appli cliente n'aura pas la même précision que le SGBDR. Autrement dit il sera impossible du fait de l'erreur d'écart d'arrondis de retouver jamais la référence de clef !!!

A +

dymezac · 27/09/2006, 09h22

bonjour et merci,

c'est vrai..
je suis rassuré maintenant

je me disais que par exemple pour une table de commandes de clients par exemple ça pouvait se produire mais c'est vrai qu'on a de la marge avec 4 milliards et meme les BIGINT

merci encore

26/09/2006, 12h35	#1
dymezac Membre du Club Inscription : janvier 2006 Messages : 224 Détails du profil Informations forums : Inscription : janvier 2006 Messages : 224 Points : 63 Points : 63	Les trous dans les clés primaires d'une base de données ? Bonjour, lors de la conception des tables d'une base de données sous MySql, nous nous posons la question suivante (valable pour les autres SGBD) : Devrais t'on utiliser l'option "auto-increment" de la clé primaire ? en effet, si le type de la clé primaire est int, supposons qu'un grand nombre d'utilisateur crée des enregistrements dans une des tables et qu'au bout d'un moment il y ait des trous dans les clé numériques (exemple simple : l'autoincrement arrive à 10 000 mais il n'y a que 300 enregistrements dans la table). A chaque fois qu'on ajoute un enregistrement l'auto increment prend le dernier numéro soit : 10 001, alors qu'en fait il aurait pu regarder quel était le prochain id disponible à partir de 1 je sais qu'on peut supprimer une colone clé primaire et la recrée pour renuméroter tout comme il faut à partir de 1, mais je pense que ça ne suit pas l'intégrité référentiel (est ce que ça modifie les clés liées, etc..) qu'en pensez vous ? y a t'il des outils pour cela dans les SGBD ? merci !
	00

26/09/2006, 16h01	#2
Tofalu Rédacteur Christophe Warin Inscription : octobre 2004 Messages : 8 635 Détails du profil Informations personnelles : Nom : Christophe Warin Âge : 28 Informations forums : Inscription : octobre 2004 Messages : 8 635 Points : 13 718 Points : 13 718	Les trous sont perdus, et ce n'est pas bien grave Imagine un processus de facturation que tu purges tout les cinq ans. Monsieur A a une facture en 2005 n° 2003 En 20010, on purge, le n°2003 est libre, monsieur B le récupère. Le 10 Juin 2011, le service compta demande de retrouver la facture de Mr A n° 2003 ... __________________ Nouveau : Access 2010 : l'avis de quelques professionnels
	00

26/09/2006, 16h37	#4
orafrance Rédacteur/Modérateur Inscription : janvier 2004 Messages : 15 861 Détails du profil Informations personnelles : Âge : 35 Informations forums : Inscription : janvier 2004 Messages : 15 861 Points : 16 212 Points : 16 212	je serais tenté de penser qui tu vas atteindre la limite de capacité physique (coté perf et coût d'exploitation) avant d'atteindre la limite de int... au pire tu met un float... pourquoi absolument un int ? __________________ Règles et liens du forum Oracle Guide Oracle Pense-bête du DBA Oracle : Exemple
	00

26/09/2006, 23h00	#6
SQLpro Rédacteur/Modérateur Frédéric BROUARD Expert SGBDR & SQL Inscription : mai 2002 Messages : 10 959 Détails du profil Informations personnelles : Nom : Frédéric BROUARD Localisation : France Informations professionnelles : Activité : Expert SGBDR & SQL Secteur : Conseil Informations forums : Inscription : mai 2002 Messages : 10 959 Points : 17 793 Points : 17 793	Vieux débat... Question sempiternelle posée 10987765789087654567 fois Réponse simple: 1) on ne recupère JAMAIS une clef utilisée. 2) à raison d'une insertion par seconde en continue dans la journée, pour épuise toutes les nombres d'un INT il faut... 66 ans. Imaginons une table des clients avec un auto incrément type INT, il y aura donc dans cette table 4 milliards de clients, soit près de 50% de la population terrestre.... A + __________________ Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/ *Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.* Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
	00

27/09/2006, 09h02	#7
SQLpro Rédacteur/Modérateur Frédéric BROUARD Expert SGBDR & SQL Inscription : mai 2002 Messages : 10 959 Détails du profil Informations personnelles : Nom : Frédéric BROUARD Localisation : France Informations professionnelles : Activité : Expert SGBDR & SQL Secteur : Conseil Informations forums : Inscription : mai 2002 Messages : 10 959 Points : 17 793 Points : 17 793	Et si cela ne suffit pas on peut passer la colonne en BIGINT, ce qui permet de prévoir large.... Pour épuiser ces nombres dans ce conctexte il faut à raison d'une insertion par seconde, quelque centaines de millénaires... D'ici là on aura le temps de voir ! Enfin, l'utilisation du FLOAT est stupide ! En effet on peut se trouver dans le cas ou il est impossible de retrouver la clef !!! Comment-est-ce possible ??? Simplement parce que l'appli cliente n'aura pas la même précision que le SGBDR. Autrement dit il sera impossible du fait de l'erreur d'écart d'arrondis de retouver jamais la référence de clef !!! A + __________________ Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/ *Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.* Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
	00

26/09/2006, 16h17	#3
dymezac Membre du Club Inscription : janvier 2006 Messages : 224 Détails du profil Informations forums : Inscription : janvier 2006 Messages : 224 Points : 63 Points : 63	bonjour et merci pour votre réponse.. mais dans le cas ou il n'y a pas besoin de retrouver l'enregistrement.. (pas de serive compta...) .. une table dont on a pas besoin de garder une trace des enregistrements, par exemple une table ou des utilisateurs peuvent ajouter et supprimer des enregistrements comme ils le veulent. Autre problème : et si au bout de 5 ans on arrive au max de int qui est d'environ 4 milliards d'enregistrement (admettons).. il va bien y avoir un probleme, comme pour le bug de l'an 2000 Enfin, n'existe t'il pas de routine, ou d'outils intégrés à certains SGBDR qui renumérotent bien les clés primaires ainsi que les numéros des clés liés dans les autres tables.. ? ça serait pourtant une solution, et c'est pourquoi je me demande si ça n'existe pas deja... si personne n'a deja réfléchit à ce probleme n'y a t'il pas de grosses bases de données qui ont atteint le max de int de leur clé primaire par exemple ?
	00

26/09/2006, 17h15	#5
dymezac Membre du Club Inscription : janvier 2006 Messages : 224 Détails du profil Informations forums : Inscription : janvier 2006 Messages : 224 Points : 63 Points : 63	oui c'est vrai.. autant prendre un float.. c'est vrai pour le problème des perfs je n'avais pas penser. comment font les gens dans ce cas la justement, quand une base de données atteint le max des perfs.. ? il faut faire du menage dans la base ? .. répartir la base de données sur plusieurs machines ? en fait donc, par rapport à ma question d'origine, il n'existe pas d'outils qui gère le renumérotage des clés primaires en gérant les clés liées dans les autres tables ??
	00

27/09/2006, 09h22	#8
dymezac Membre du Club Inscription : janvier 2006 Messages : 224 Détails du profil Informations forums : Inscription : janvier 2006 Messages : 224 Points : 63 Points : 63	bonjour et merci, c'est vrai.. je suis rassuré maintenant je me disais que par exemple pour une table de commandes de clients par exemple ça pouvait se produire mais c'est vrai qu'on a de la marge avec 4 milliards et meme les BIGINT merci encore
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email