È buona prassi avere sempre una chiave primaria intera con autoincremento?

Non è mai una cattiva idea avere un identificatore di riga univoco garantito. Credo che non dovrei dire mai - ma andiamo con la stragrande maggioranza delle volte è una buona idea.

I potenziali svantaggi teorici includono un indice extra da mantenere e uno spazio di archiviazione aggiuntivo utilizzato. Non è mai stato abbastanza un motivo per me non usarne uno.

Non sono d'accordo con tutte le risposte precedenti. Ci sono molte ragioni per cui è una cattiva idea aggiungere un campo di incremento automatico in tutte le tabelle.

Se hai una tabella in cui non ci sono chiavi evidenti, un campo di auto-incremento sembra una buona idea. Dopo tutto, non vuoi select * from blog where body = '[10000 character string]' . Preferiresti select * from blog where id = 42 . Direi che nella maggior parte di questi casi, quello che vuoi veramente è un identificatore univoco; non un identificatore univoco sequenziale. Probabilmente vorrai usare un identificatore universalmente univoco.

Ci sono funzioni nella maggior parte dei database per generare identificatori univoci casuali ( uuid in mysql, postgres. newid in mssql). Questi ti permettono di generare dati in più database, su macchine diverse, in qualsiasi momento, senza alcuna connessione di rete tra loro, e ancora unire i dati con zero conflitti. Ciò consente di configurare più facilmente più server e persino data center, come ad esempio con i microservizi.

Questo evita anche che gli aggressori indovino l'url alle pagine a cui non dovrebbero avere accesso. Se c'è un https://example.com/user/1263 probabilmente c'è anche un https://example.com/user/1262 . Ciò potrebbe consentire l'automazione di un exploit di sicurezza nella pagina del profilo utente.

Ci sono anche molti casi in cui una colonna uuid è inutile o addirittura dannosa. Diciamo che hai un social network. C'è una tabella users e una tabella friends . La tabella degli amici contiene due colonne ID utente e un campo di incremento automatico. Vuoi 3 essere amici con 5 , quindi inserisci 3,5 nel database. Il database aggiunge un ID di incremento automatico e memorizza 1,3,5 . In qualche modo, l'utente 3 fa nuovamente clic sul pulsante "aggiungi amico". Inserisci nuovamente 3,5 nel database, il database aggiunge un ID di incremento automatico e inserisce 2,3,5 . Ma ora 3 e 5 sono amici due volte! Questo è uno spreco di spazio, e se ci pensate, lo è anche la colonna di incremento automatico. Tutto quello che devi sapere se a e b sono amici è selezionare per la riga con questi due valori. Sono, insieme, un identificatore di riga univoco. (Probabilmente vorrai scrivere una logica per assicurarti che 3,5 e 5,3 siano deduplicati.)

Ci sono ancora casi in cui gli ID sequenziali possono essere utili, come quando si costruisce un url-shortener, ma soprattutto (e anche con l'url shortener) un id univoco generato a caso è quello che si vuole veramente usare.

TL; DR: Utilizza gli UUID anziché gli incrementi automatici, se non hai già un modo univoco di identificare ogni riga.

Le chiavi automatiche hanno principalmente vantaggi.

Ma alcuni possibili svantaggi potrebbero essere:

• Se si dispone di una chiave aziendale, è necessario aggiungere un indice univoco anche a quella colonna per applicare le regole aziendali.
• Quando si trasferiscono dati tra due database, specialmente quando i dati si trovano in più di una tabella (es. master / dettaglio), non è diretto in quanto le sequenze non sono sincronizzate tra database e sarà necessario creare una tabella di equivalenza per prima cosa utilizzare la chiave aziendale come corrispondenza per sapere quale ID del database di origine corrisponde a quale ID nel database di destinazione. Tuttavia, non dovrebbe esserci un problema durante il trasferimento dei dati da / a tabelle isolate.
• Molte aziende dispongono di strumenti di reporting drag-and-drop ad-hoc, grafici, point-and-click. Dal momento che gli ID autoincrementali sono privi di significato, questo tipo di utenti troverà difficile dare un senso ai dati al di fuori di "l'app".
• Se modifichi accidentalmente la chiave aziendale, è probabile che non recupererai mai quella riga perché non hai più qualcosa da identificare per gli umani. Ciò ha causato un errore nella piattaforma BitCoin una volta .
• Alcuni designer aggiungono un ID a una tabella di join tra due tabelle, quando il PK deve semplicemente essere composto dai due ID esterni. Ovviamente se la tabella di join si trova tra tre o più tabelle, allora un ID autoincrementale ha senso, ma poi devi aggiungere una chiave univoca quando si applica alla combinazione di FK per applicare le regole di business.

Ecco una sezione di articolo di Wikipedia sugli svantaggi delle chiavi surrogate.

Solo per essere contrari, No, NON è necessario avere sempre un PK AutoInc numerico.

Se analizzi attentamente i tuoi dati, spesso identifichi le chiavi naturali nei dati. Questo è spesso il caso in cui i dati hanno un significato intrinseco al business. A volte i PK sono artefatti di sistemi antichi che gli utenti aziendali utilizzano come seconda lingua per descrivere gli attributi del loro sistema. Ad esempio, ho visto i numeri VIN dei veicoli come chiave primaria di una tabella "Veicolo" in un sistema di gestione della flotta.

Tuttavia ha avuto origine, SE hai già un identificatore univoco, usalo. Non creare una seconda chiave primaria priva di significato; è uno spreco e potrebbe causare errori.

A volte è possibile utilizzare un PK AutoInc per generare un valore significativo per il cliente, ad es. Numeri di polizza Impostare il valore iniziale su qualcosa di sensato e applicare regole di business su zeri iniziali, ecc. Questo è probabilmente un approccio "il meglio dei due mondi".

Quando si dispone di un numero ridotto di valori relativamente statici, utilizzare i valori appropriati per l'utente del sistema. Perché usare 1,2,3 quando si può usare L, C, H dove L, H e C rappresentano Vita, Auto e Casa in un contesto di "Tipo di politica" assicurativo, o, ritornando all'esempio VIN, come usare "TO "per la Toyota? Tutte le vetture Toyata hanno un VIN che inizia "TO" È una cosa in meno per gli utenti da ricordare, rende meno probabile che introducano errori di programmazione e degli utenti e potrebbe anche essere un surrogato utilizzabile per una descrizione completa nei report di gestione rendendo i report più semplici scrivere e forse più veloce da generare.

Un ulteriore sviluppo di questo è probabilmente "un ponte troppo lontano" e generalmente non lo raccomando, ma lo includo per completezza e potresti trovarne un buon uso. Cioè, usa la descrizione come chiave primaria. Per i dati che cambiano rapidamente questo è un abominio. Per i molto dati statici riportati su All The Time , forse no. Basta menzionarlo in modo che sia seduto lì come possibilità.

IO uso i PK AutoInc, mi limito a coinvolgere il mio cervello e cercare prima le alternative migliori. L'arte della progettazione di database sta facendo qualcosa di significativo che può essere interrogato rapidamente. Avere troppi join lo impedisce.

EDIT Un altro caso cruciale in cui non è necessario un PK autogenerato è il caso di tabelle che rappresentano l'intersezione di altre due tabelle. Per attaccare con l'analogia Car, A Car ha 0 .. accessorys, ogni accessorio può essere trovato su molte auto. Quindi per rappresentarlo, crei una tabella Car_Accessory contenente i PK di Car e Accessory e altre informazioni pertinenti sul link Date ecc.

Quello che non serve (di solito) è un PK AutoInc su questo tavolo - sarà accessibile solo tramite l'auto "dimmi quali accessori sono su questa macchina" o dall'accessorio "dimmi che auto hanno questo accessorio "

Molte tabelle hanno già un ID univoco naturale. Non aggiungere un'altra colonna ID univoca (incremento automatico o altro) su queste tabelle. Usa invece l'ID univoco naturale. Se aggiungi un altro ID univoco, hai essenzialmente una ridondanza (duplicazione o dipendenza) nei tuoi dati. Questo va contro i principi della normalizzazione. Un unico ID dipende dall'altra per la precisione. Ciò significa che devono essere mantenuti perfettamente sincronizzati in tutte le volte in ogni sistema che gestisce queste righe. È solo un'altra fragilità nell'integrità dei dati che non vuoi veramente dover gestire e convalidare a lungo termine.

La maggior parte delle tabelle in questi giorni non ha realmente bisogno del potenziamento delle prestazioni molto minore che una colonna id univoca aggiuntiva darebbe (e talvolta addirittura riduce le prestazioni). Come regola generale in IT, evita ridondanza come la piaga! Resisti ovunque ti sia suggerito. È un anatema. E presta attenzione alla citazione. Tutto dovrebbe essere il più semplice possibile, ma non più semplice. Non hai due ID univoci in cui uno sarà sufficiente, anche se quello naturale sembra meno ordinato.

Su sistemi più grandi, l'ID è booster di consistenza, lo si usa quasi ovunque. In questo contesto, le singole chiavi primarie NON sono raccomandate, sono costose nella riga inferiore (leggi perché).

Ogni regola ha un'eccezione, quindi potrebbe non essere necessario l'ID di autoincremento intero nelle tabelle di staging utilizzate per l'esportazione / importazione e su tabelle unidirezionali o tabelle temporanee simili. Preferiresti anche i GUID anziché gli ID sui sistemi distribuiti.

Molte risposte suggeriscono che la chiave univoca esistente dovrebbe essere presa. Bene, anche se ha 150 caratteri? Io non la penso così

Ora il mio punto principale:

Sembra che gli avversari dell'identificatore intero di autoincremento parlino di piccoli database con un massimo di 20 tabelle. Lì possono permettersi un approccio individuale a ciascun tavolo.

MA una volta che hai un ERP con oltre 400 tabelle, con ID intero automatico intero ovunque (eccetto i casi citati sopra) ha solo un senso. Non ti affidi ad altro campi unici anche se sono presenti e protetti per unicità.

• Beneficiate di una convenzione universale che consente di risparmiare tempo, risparmiare fatica e ricordare facilmente
• Nella maggior parte dei casi hai JOIN tabelle, senza bisogno di controllare quali sono le chiavi.
• È possibile utilizzare routine di codice universali che funzionano con la colonna di incremento automatico dei numeri interi.
• Puoi estendere il tuo sistema con nuove tabelle o plugin utente non previsti prima semplicemente facendo riferimento agli ID delle tabelle esistenti. Sono già lì dall'inizio, senza costi aggiuntivi per aggiungerli.

Su sistemi più grandi, può valere la pena di ignorare i minori vantaggi di quelle singole chiavi primarie e utilizzare in modo coerente l'ID autoincrement intero intero nella maggior parte dei casi. L'utilizzo di campi univoci esistenti come chiavi primarie può forse salvare alcuni byte per record, ma la memoria aggiuntiva o il tempo di indicizzazione pongono nessun problema nei moderni motori di database. In realtà stai perdendo molto più denaro e risorse in termini di tempo sprecato dagli sviluppatori / manutentori. Il software di oggi dovrebbe essere ottimizzato per il tempo e l'impegno dei programmatori: quale approccio con gli ID coerenti soddisfa molto meglio.

Non è una buona pratica per i disegni superflui. Cioè - Non è consigliabile avere sempre una chiave primaria di incremento automatico int quando non è necessaria.

Vediamo un esempio in cui non è necessario.

Hai una tabella per gli articoli: questa ha una chiave primaria int id e una colonna varchar denominata title .

Hai anche una tabella piena di categorie di articoli- id int chiave primaria, varchar name .

Una riga nella tabella Articoli ha un id di 5 e un title "Come cucinare l'oca con il burro". Vuoi collegare l'articolo con le seguenti righe nella tua tabella Categorie: "Fowl" ( id : 20), "Goose" ( id : 12), "Cooking" ( id : 2), "Butter" (id: 9).

Ora hai 2 tabelle: articoli e categorie. Come crei la relazione tra i due?

Potresti avere una tabella con 3 colonne: id (chiave primaria), article_id (chiave esterna), category_id (chiave esterna). Ma ora hai qualcosa come:

| id | a_id | c_id | 
| 1  |  5   |   20 | 
| 2  |  5   |   12 | 
| 3  |  5   |    2 | 

Una soluzione migliore è avere una chiave primaria composta da 2 colonne.

| a_id | c_id | 
|    5 |   20 | 
|    5 |   12 | 
|    5 |    2 | 

Questo può essere ottenuto facendo:

create table articles_categories (
  article_id bigint,
  category_id bigint,
  primary key (article_id, category_id)
) engine=InnoDB;

Un altro motivo per non utilizzare un intero di incremento automatico è se si utilizzano gli UUID per la chiave primaria.

Gli UUID sono per loro unica definizione, che realizza la stessa cosa che usa gli interi unici. Hanno anche i propri vantaggi aggiuntivi (e contro) sugli interi. Ad esempio, con un UUID, sai che la stringa univoca a cui ti stai riferendo punta ad un particolare record di dati; questo è utile nei casi in cui non si dispone di un database centrale o dove le applicazioni hanno la possibilità di creare record di dati offline (quindi caricarli sul database in un secondo momento).

Alla fine, non devi pensare alle chiavi primarie come a una cosa. Devi pensare a loro come alla funzione che svolgono. Perché hai bisogno di chiavi primarie? Per essere in grado di identificare in modo univoco set specifici di dati da una tabella utilizzando un campo che non verrà modificato in futuro. Hai bisogno di una particolare colonna chiamata id per fare questo, o puoi basare questa identificazione univoca su altri dati (immutabili)?

Or are there scenarios where you don't want to add such a field?

Certo.

Prima di tutto, ci sono database che non hanno autoincrementi (ad es. Oracle, che certamente non è uno dei contendenti più piccoli in circolazione). Questa dovrebbe essere una prima indicazione che non tutti amano o hanno bisogno di loro.

Ancora più importante, pensa a ciò che l'ID è in realtà: è una chiave primaria per i tuoi dati. Se hai una tabella con una chiave primaria diversa, non hai bisogno di un ID e non dovresti averne uno. Ad esempio, una tabella (EMPLOYEE_ID, TEAM_ID) (in cui ciascun dipendente può essere in più team contemporaneamente) ha una chiave primaria chiaramente definita costituita da questi due ID. Aggiungere una colonna ID autoincrement, che è anche una chiave primaria per questa tabella, non avrebbe alcun senso. Ora stai trascinando 2 tasti primari in giro e la prima parola in "chiave primaria" dovrebbe darti un suggerimento che dovresti davvero avere solo uno.

Di solito uso una colonna "identità" (numero intero autoincrostante) quando definisco nuove tabelle per dati "longevi" (i record mi aspetto di inserirli una volta e di mantenerli indefinitamente anche se finiscono "cancellati logicamente" impostando un campo di bit).

Ci sono alcune situazioni a cui posso pensare quando non vuoi usarle, la maggior parte delle quali si riduce a scenari in cui una tabella su un'istanza del DB non può essere la fonte autorevole per i nuovi valori ID:

• Quando gli ID incrementali sarebbero troppe informazioni per un potenziale aggressore. L'uso di una colonna di identità per i servizi dati "pubblici" ti rende vulnerabile al "problema Tank tedesco"; se esiste l'ID record 10234, è ovvio che il record 10233, 10232, ecc. esiste, almeno fino a 10001 record, e quindi è facile controllare i record 1001, 101 e 1 per capire dove è stata avviata la colonna Identity. I GUID V4 composti principalmente da dati casuali interrompono questo comportamento incrementale in base alla progettazione, pertanto, poiché esiste solo un GUID, un GUID creato incrementando o decrementando un byte del GUID non esiste necessariamente, rendendo più difficile per un utente malintenzionato utilizzare un servizio indotto per il recupero di record singoli come strumento di dumping. Esistono altre misure di sicurezza che possono limitare l'accesso, ma questo aiuta.
• In M: M tabelle di riferimento incrociato. Questa è una specie di dammi ma l'ho già vista prima. Se si dispone di una relazione molti a molti tra due tabelle nel database, la soluzione ideale è una tabella di riferimenti incrociati contenente colonne di chiavi esterne che fanno riferimento al PK di ciascuna tabella. Il PK di questa tabella dovrebbe essere praticamente sempre una chiave composta delle due chiavi esterne, per ottenere il comportamento dell'indice incorporato e garantire l'univocità dei riferimenti.
• Quando pianifichi di inserire ed eliminare in blocco molte tabelle. Probabilmente il più grande svantaggio delle colonne Identity è il trambusto extra che devi percorrere quando fai un inserimento di righe da un'altra tabella o query, dove si desidera mantenere i valori chiave della tabella originale. Devi attivare "l'inserimento dell'identità" (comunque è fatto nel tuo DBMS), quindi assicurati manualmente che le chiavi che stai inserendo siano univoche, e quando hai finito con l'importazione devi impostare il contatore dell'identità nel i metadati della tabella fino al valore massimo presente. Se questa operazione si verifica molto su questa tabella, considera uno schema PK diverso.
• Per le tabelle distribuite. Le colonne di identità funzionano in modo ottimale per database a istanza singola, coppie di failover e altri scenari in cui un'istanza di database è l'unica autorità sull'intero schema di dati in un dato momento. Tuttavia, c'è solo così grande che puoi andare e avere ancora un computer abbastanza veloce. La replica o la registrazione del log delle transazioni possono ottenere ulteriori copie di sola lettura, ma esiste anche un limite alla scala di tale soluzione. Prima o poi avrai bisogno di due o più istanze del server che gestiscono gli inserimenti di dati e poi si sincronizzeranno l'un l'altro. Quando si verifica questa situazione, ti consigliamo un campo GUID invece di uno incrementale, perché la maggior parte dei DBMS viene preconfigurata per utilizzare una parte dei GUID che generano come identificatore specifico dell'istanza, quindi genera il resto dell'identificatore in modo casuale o in modo incrementale. In entrambi i casi, le probabilità di una collisione tra due generatori GUID sono nulle, mentre una colonna intera di identità è un incubo da gestire in questa situazione (puoi andare pari / dispari compensando i semi e impostando l'incremento su 2, ma se un server vede più attività rispetto alle altre che stai sprecando ID).
• Quando devi applicare l'univocità su più tabelle nel DB. È comune nei sistemi di contabilità, ad esempio, gestire la contabilità generale (con una riga per ogni credito o debito di ogni account che sia mai accaduto , quindi diventa molto grande molto rapidamente) come una sequenza di tabelle ognuna rappresentante un mese / anno di calendario. Le viste possono quindi essere create per unirle per la segnalazione. Logicamente, questa è tutta una tabella molto grande, ma tagliandola rende più facili i lavori di manutenzione del DB. Tuttavia, presenta il problema di come gestire gli inserimenti in più tabelle (consentendo di iniziare a registrare le transazioni nel mese successivo mentre si chiude ancora l'ultima) senza finire con chiavi duplicate. Anche in questo caso, i GUID anziché le colonne di numeri interi sono la soluzione ideale, poiché il DBMS è progettato per generarli in un modo davvero unico, in modo che un singolo valore GUID venga visualizzato una sola volta nell'intero DBMS.

Esistono soluzioni alternative che consentono l'utilizzo di colonne di identità in queste situazioni, come spero che abbia menzionato, ma nella maggior parte di queste l'aggiornamento dalla colonna di identità intera a un GUID è più semplice e risolve il problema in modo più completo.

Una chiave primaria auto-incrementata (identità) è una buona idea, tranne notare che non ha senso al di fuori del contesto del database e dei client immediati di quel database. Ad esempio, se si trasferiscono e archiviano alcuni dati in un altro database, quindi si procede a scrivere dati diversi su entrambe le tabelle del database, gli ID divergeranno, ovvero i dati con un ID di 42 in un database non corrisponderanno necessariamente ai dati con un id di 42 nell'altra.

Dato questo, se è necessario essere ancora in grado di identificare le file in modo univoco al di fuori del database (e spesso lo è), allora è necessario avere una chiave diversa per questo scopo. Una chiave aziendale accuratamente selezionata andrà bene, ma spesso ti ritroverai nella posizione di un gran numero di colonne necessarie per garantire unicità. Un'altra tecnica consiste nel disporre di una colonna Id come chiave primaria clusterizzata con incremento automatico e un'altra colonna uniqueidentifier (guid) come chiave univoca non raggruppata, allo scopo di identificare in modo univoco la riga ovunque essa esista nel mondo. Il motivo per cui in questo caso è ancora presente una chiave auto-incrementata è perché è più efficiente raggruppare e indicizzare la chiave di incremento automatico piuttosto che fare lo stesso con una guida.

Un caso in cui potresti non volere che una chiave a incremento automatico sia una tabella molti-a-molti in cui la chiave primaria è un composto delle colonne Id di altre due tabelle (potresti ancora avere una chiave autoincrementante qui ma non ne vedo il senso).

Un'altra domanda è il tipo di dati della chiave auto-incrementata. L'utilizzo di Int32 offre un intervallo di valori ampio ma relativamente limitato. Personalmente utilizzo spesso le colonne di bigint per l'ID, in modo da non dover praticamente mai preoccuparmi di esaurire i valori.

Come altre persone hanno avanzato il caso per una chiave primaria incrementale, ne creerò una per un GUID:

• È garantito che sia unico
• È possibile avere un viaggio in meno nel database per i dati nell'applicazione. (Per una tabella tipi, ad esempio, è possibile memorizzare il GUID nell'applicazione e utilizzarlo per recuperare il record. Se si utilizza un'identità, è necessario eseguire una query sul database in base al nome e ho visto molte applicazioni che eseguono questa operazione per ottenere il PK e più tardi lo interroga nuovamente per ottenere tutti i dettagli).
• È utile per nascondere i dati. www.domain.com/Article/2 Mi fa sapere che hai solo due articoli, mentre www.domain.com/article/b08a91c5-67fc-449f-8a50-ffdf2403444a non mi dice nulla.
• È possibile unire facilmente record da diversi database.
• MSFT utilizza GUID per l'identità.

Modifica: punto duplicato

Come principio di buona progettazione, ogni tabella dovrebbe avere un modo affidabile per identificare in modo univoco una riga. Sebbene sia quello a cui si riferisce una chiave primaria, non sempre richiede l'esistenza di una chiave primaria. L'aggiunta di una chiave primaria a ogni tabella non è una cattiva pratica poiché fornisce un'identificazione di riga univoca, ma potrebbe non essere necessaria.

Per mantenere relazioni affidabili tra le righe di due o più tabelle, è necessario farlo tramite chiavi esterne, quindi la necessità di chiavi primarie in almeno alcune tabelle. L'aggiunta di una chiave primaria a ogni tabella semplifica l'estensione della progettazione del database quando arriva il momento di aggiungere nuove tabelle o relazioni ai dati esistenti. Pianificare in anticipo è sempre una buona cosa.

Come principio di base (forse una regola difficile), il valore di una chiave primaria non dovrebbe mai cambiare durante la vita della sua riga. È consigliabile presumere che qualsiasi dato aziendale di una riga sia soggetto a modifiche nel corso della sua durata, pertanto qualsiasi dato aziendale sarà un candidato scadente per una chiave primaria. Questo è il motivo per cui qualcosa di astratto come un intero auto-incrementato è spesso una buona idea. Tuttavia, gli interi auto-incrementati hanno i loro limiti.

Se i tuoi dati avranno solo una vita all'interno del tuo database, gli interi auto-incrementati andranno bene. Ma, come è stato menzionato in altre risposte, se vuoi che i tuoi dati siano condivisi, sincronizzati, o comunque abbiano una vita al di fuori del tuo database, gli interi autoincrementati rendono scarse le chiavi primarie. Una scelta migliore sarà un guid (aka uuid "universally unique id").

La domanda, e molte delle risposte, manca il punto importante che tutte le chiavi naturali per ogni tabella risiedono esclusivamente nello schema logico per il database, e tutte le chiavi surrogate per ogni tabella risiedono esclusivamente nello schema fisico per il database. altre risposte discutono esclusivamente dei vantaggi relativi dei numeri interi rispetto alle chiavi surrogate GUID, senza discutere i motivi per cui le chiavi sostitutive vengono utilizzate correttamente e quando.

BTW: Evitiamo l'uso del termine impreciso chiave primaria . È un artefatto di modelli di dati pre-relazionali che è stato prima cooptato (incautamente) nel modello relazionale, e poi cooptato nuovamente nel dominio fisico da vari fornitori RDBMS. Il suo uso serve solo a confondere la semantica.

Nota dal modello relazionale che, affinché lo schema logico del database sia in prima forma normale , ogni tabella deve avere un set di campi visibile all'utente, noto come chiave naturale che identifica in modo univoco ogni riga della tabella. Nella maggior parte dei casi una tale chiave naturale viene prontamente identificata, ma a volte bisogna costruirla, sia come campo tie breaker che in altro modo. Tuttavia, una chiave così costruita rimane sempre visibile all'utente e quindi risiede sempre nello schema logico del database.

Per contrasto, qualsiasi chiave surrogata su una tabella risiede puramente nello schema fisico per il database (e quindi deve sempre, sia per ragioni di sicurezza che per la manutenzione dell'integrità del database , essere completamente invisibile agli utenti del database). L'unica ragione per introdurre una chiave surrogata è di risolvere i problemi di prestazioni nella manutenzione fisica e uso del DB; se questi siano join, replica, più fonti hardware per dati o altro.

Poiché l'unica ragione per l'introduzione di una chiave surrogata è la performance, supponiamo che desideriamo che sia performante. Se il problema delle prestazioni a portata di mano si unisce, desideriamo necessariamente rendere la nostra chiave surrogata il più restrittiva possibile (senza intralciare l'hardware, quindi di solito interi e byte brevi sono fuori). Unire le prestazioni dipende dall'altezza minima dell'indice, quindi un intero a 4 byte è una soluzione naturale. Se il tuo problema di prestazioni è la frequenza di inserimento, un numero intero a 4 byte può anche essere una soluzione naturale (a seconda degli interni del tuo RDBMS). Se il problema di prestazioni di una tabella è la replica o più origini dati rispetto a qualche altra tecnologia surrogata chiave , può essere più adatto un GUID o una chiave in due parti (ID host + numero intero). Non sono personalmente uno dei preferiti di GUID ma sono convenienti.

Per riassumere, non tutte le tabelle richiedono una chiave surrogata (di qualsiasi tipo); dovrebbero essere utilizzati solo quando ritenuto necessario per l'esecuzione della tabella in esame. Indipendentemente dalla tecnologia di surrogate key che preferisci, pensa attentamente alle reali esigenze del tavolo prima di fare una scelta; cambiare la scelta della tecnologia surrogate key per un tavolo sarà un lavoro estenuante. Documenta la metrica delle prestazioni chiave per la tua tabella in modo che i tuoi successori comprenderanno le scelte fatte.

Casi speciali

1. Se i requisiti aziendali impongono una numerazione sequenziale di transazioni per scopi di controllo (o altri) rispetto a quel campo è non una chiave surrogata; è una chiave naturale (con requisiti aggiuntivi). Dalla documentazione un intero autoincrementante genera solo chiavi surrogate , quindi trova un altro meccanismo per generarlo. Ovviamente sarà necessario un qualche tipo di monitor, e se si stanno acquistando le transazioni da più siti, un sito sarà speciale , in quanto è il sito host designato per il tenere sotto controllo.

2. Se la tua tabella non sarà mai più di un centinaio di righe, allora l'altezza dell'indice è irrilevante; ogni accesso avverrà mediante una scansione della tabella. Tuttavia i confronti tra stringhe su stringhe lunghe saranno ancora molto più costosi del confronto di un intero a 4 byte e più costosi del confronto di un GUID.

3. Una tabella di valori code codificati da un campo char (4) code dovrebbe essere tanto performante quanto uno con un 4 -byte intero. Anche se non ne ho la prova, uso spesso l'ipotesi e non ho mai avuto motivo di rimproverarla.

Non solo non è una buona pratica, anzi è descritta come un anti-pattern nel libro SQL Antipatterns di Bill Karwin.

Non tutte le tabelle hanno bisogno di una pseudokey - una chiave primaria con un valore arbitrario, non qualcosa che ha valore semantico per il modello - e non c'è motivo di chiamarla sempre id .

Questo è abbastanza universale, altrimenti è necessario verificare che la chiave sia effettivamente unica. Questo sarebbe fatto guardando tutte le altre chiavi ... che richiederebbero molto tempo. Avere una chiave incrementale diventa costoso quando il numero di record si avvicina al valore di overflow della chiave.

Solitamente faccio i puntatori a nomi di campo più ovvi come ref_{table} o un'idea simile.

Se non è necessario puntare esternamente su un record, non è necessario un ID.

Non direi che dovrebbe essere sempre . Ho un tavolo qui senza chiave unica e non ne ha bisogno. È un registro di controllo. Non ci sarà mai un aggiornamento, le query restituiranno tutte le modifiche a ciò che viene registrato, ma questo è il meglio che può essere ragionevolmente fatto da un umano per definire una modifica errata. (Se il codice potrebbe non averlo permesso, in primo luogo!)

Un contatore di incremento automatico per una chiave primaria non è una buona idea. Questo perché è necessario tornare al database per trovare la chiave successiva e incrementare di un'unità prima di inserire i dati.

Detto questo, generalmente utilizzerei qualsiasi cosa il database possa fornire per la chiave primaria piuttosto che averla come parte dell'applicazione.

Consentendo al database di fornirlo in modo nativo, è possibile garantire che la chiave sia unica per ciò di cui ha bisogno.

Naturalmente non tutti i database lo supportano. In tal caso, generalmente utilizzo una tabella che memorizza i bucket delle chiavi e utilizza intervalli di alta e bassa gestione nell'applicazione. Questa è la soluzione più performante che trovo perché ottieni un intervallo di 10000 numeri e li incrementale automaticamente nell'istanza dell'applicazione. Un'altra istanza dell'applicazione può raccogliere un altro numero di numeri con cui lavorare. È necessaria una primitiva di chiave primaria sufficientemente grande, ad esempio una lunghezza di 64 bit.

UUID Non uso come chiavi primarie perché il costo di costruirle e memorizzarle è molto più alto dell'incremento di un valore lungo di uno. Gli UUID trattano ancora il paradosso del compleanno in quanto un duplicato può teoricamente verificarsi.