Esiste un valore che denota una chiave come "Primaria"?

Dal punto di vista logico e pratico, non ci sono in realtà chiavi "primarie" nel modello relazionale [1] perché tutte le chiavi candidate possono avere esattamente lo stesso status, caratteristiche e funzione nel modello relazionale: le chiavi candidate sono identificatori irriducibilmente univoci, non annullabili per le tuple in una relazione.

La pratica di individuare una qualsiasi chiave come "preferita", "più importante" o per qualsiasi altro scopo è una questione di convenzione e convenienza piuttosto che di necessità. Se una relazione ha una sola chiave candidata, potremmo anche chiamarla una chiave "primaria" per impostazione predefinita. Se una relazione ha più di una chiave, la scelta di quale chiamare "primaria" è importante solo quanto si desidera e il significato di tale scelta è definito dall'utente e / o dalla sintassi e dalle funzioni DBMS utilizzate per definiscilo.

Non sei certo solo nel trovare l'idea di designare una chiave "primaria" di valore un po 'limitato e dubbio. Soprattutto nei sistemi di database di oggi, rispetto a quelli dei decenni passati. Il "presupposto della chiave primaria" è una nozione controverso che è probabilmente superata e probabilmente ora i diritti dovrebbero essere ritirati. Sfortunatamente è un'ipotesi che è strongmente radicata nella mentalità e nelle pratiche di molti esperti di database.

[1] EFCodd originariamente utilizzava il termine "chiave primaria" per indicare qualsiasi e tutte chiavi di una relazione e affermava che una relazione poteva avere più di una di queste chiave. Solo in seguito il termine "chiave del candidato" è apparso nella letteratura del database relazionale e il termine "chiave primaria" ha assunto il suo moderno significato di denotare una sola chiave per relazione.

Come principio generale, aggiungo sommariamente un campo "autonumber" a ogni tabella che creo in un database relazionale, gli assegno un nome ragionevole e lo contrassegno come chiave primaria. In questo modo vengono eliminati tutti i tipi di complessità, come i concetti "candidato", "secondario" e "terziario".

Le chiavi primarie sono ben comprese per avere determinate caratteristiche: sono uniche e non annullabili, tra le altre cose. Contrassegnando una chiave, una chiave primaria rende molto chiaro agli altri sviluppatori qual è il suo scopo e a cosa può essere effettivamente utilizzato.

Per lo più, è garantito che sia un identificatore univoco e stabile per ogni record, rendendo possibile che la chiave partecipi ai join con tabelle esterne. Penso che mi strapperei i capelli se dovessi sistematicamente fare i conti con tabelle contenenti chiavi composte, terziarie e secondarie, e dovevo capire ogni volta come ottenere un identificatore univoco in modo che potessi unirmi a una query.

Tecnicamente parlando, una chiave primaria non è diversa da qualsiasi altra chiave non nullable univoca: è possibile ottenere le stesse restrizioni dello schema semplicemente rendendo le colonne non annullabili (e anche quella non è realmente un requisito IIRC), e definendo una chiave univoca per loro.

Tuttavia, dichiarare una chiave come PRIMARY segnala che si intende che questa particolare chiave sia il modo predefinito di fare riferimento alle righe in questa tabella. Questo indicatore è importante per le altre persone che lavorano con il tuo schema, perché dice loro che possono aspettarsi che questa chiave rimanga intatta e che controllerai le eventuali chiavi esterne in altre tabelle che fanno riferimento a esso prima di cambiarlo. Ma è anche interessante per il motore di database, se non altro perché queste informazioni extra possono essere utilizzate per l'ottimizzazione delle query e l'euristica di memorizzazione nella cache.

Se una tabella ha tre chiavi candidate, tutte uniche, non annullabili e immutabili, la scelta migliore per una chiave primaria è la chiave candidata più piccola (sia in termini di numero di campi e dimensioni del campo) che è di dimensioni sufficienti che non sarai mai a corto di valori possibili per memorizzarlo. Idealmente, la chiave primaria di una tabella dovrebbe essere un campo (che, insieme ai vantaggi di indipendenza e immutabilità dei dati, rende i campi autonumber una scelta piuttosto facile per molti DBA, con GUID al secondo vicino).

La chiave primaria ha un significato concettuale speciale rispetto a qualsiasi altra chiave candidata, perché è quella che viene utilizzata per fare riferimento a quel record come chiave esterna su qualsiasi altra tabella. Ogni RDBMS posso pensare di far rispettare questo; non è possibile specificare un indice univoco come chiave esterna; deve essere la chiave primaria. Le chiavi esterne, a loro volta, impongono l'integrità referenziale; il valore memorizzato come riferimento di chiave esterna in un record di un'altra tabella deve esistere come chiave primaria della tabella di riferimento, ovvero non è possibile salvare un record che fa riferimento a un valore PK inesistente e si può ' t cancella un record il cui valore PK è referenziato da un altro record come chiave esterna.

Ora potresti dire che puoi unire le tabelle su qualsiasi campo che ti piace, incluso un indice univoco per un'altra chiave candidata. È corretto. Si potrebbe anche dire che è possibile definire vincoli che richiedono il valore di esistere in una query dell'altra tabella e trigger per impedire l'eliminazione di un record a cui fa riferimento un'altra tabella. Anche questo è corretto. La mia domanda per te sarebbe " WHY? ". Le chiavi esterne esistono per fornire tutte queste funzionalità; perché reinventare la ruota, soprattutto quando richiede così tanto lavoro per farlo?

Tornando a cose di cui non volevi parlare, ma che sono significative quando prendi decisioni di keying, praticamente tutti gli RDBMS (non solo MySQL) definiscono la chiave primaria come un indice, che determina come i dati per la tabella sono strutturati nei file di dati sottostanti. In MSSQL, questo è un indice "cluster", il che significa che i record sono disposti e raggruppati all'interno del filesystem in base al loro PK, oltre al PK che appare negli alberi di ricerca. Non tutti gli RDBMS organizzano i loro dati allo stesso modo, ma poiché l'accesso sequenziale ai dati è spesso importante per le prestazioni, l'indicizzazione corretta è la chiave per lavorare con realizzazioni RDBMS reali in modo efficiente.

Un indice "Primario" è quello che identifica univocamente e canonicamente un record. Se un tavolo fosse dipendente, potremmo avere le chiavi sul loro nome, ssn e data di assunzione, ma sarebbe l'id del dipendente che può identificarle canonicamente e sarebbe la chiave primaria della tabella.

Senza un indice primario, non hai un designatore formale del modo corretto per un'altra tabella per fare riferimento a un record nella tua tabella. Quindi, per estendere il mio esempio sopra, potresti avere il libro paga indicizzato da ssn e le valutazioni indicizzate dalla data di assunzione, richiedendo che la tabella dei dipendenti effettivi venga referenziata se si desidera interrogare le valutazioni rispetto alle buste paga.

Un database ha bisogno di un modo per identificare in modo univoco ogni record in ogni tabella. Ha bisogno di un solo modo, anche se esistono diverse possibilità. Ma questo è usato per indicizzare la tabella e per assicurarsi che le query di azione abbiano effetto solo sui record che si desidera influenzare. Poiché è necessario sapere quale usare, è necessario definire una delle chiavi possibili come principale. Ciò non significa che gli altri siano meno importanti o che non siano anche unici e non annullabili. Gli altri possono e devono essere indicizzati in modo univoco e possono anche partecipare ai join alle tabelle correlate, se necessario. Ma una sola cosa deve essere ciò che il database utilizza per identificare univocamente il record perché questa chiave verrà aggiunta a tutti gli indici (almeno in SQL Server). Questo dovrebbe essere il meno mutevole delle chiavi candidate in generale. L'aggiornamento delle chiavi primarie (specialmente di quelle che sono anche le FK nelle tabelle correlate) può essere un compito costoso. Rende inoltre più coerenti le relazioni con altre tabelle. Se hai mai avuto il dispiacere di lavorare con un database in cui non esisteva una chiave coerente da utilizzare per i join, dovresti capire quanto sia imporatnt questo tipo di coerenza.

TLDR Qual è il valore di un'annotazione della chiave primaria? La chiave primaria indica il modo più rapido per localizzare un record. Come hai menzionato nella tua domanda, la maggior parte delle implementazioni SQL usa la chiave primaria per indicare dove nel file della tabella è archiviato il record. L'utilizzo di un tasto numerico auto-incrementante evita la frammentazione non necessaria del file. (Riduzione delle dimensioni del file e del tempo di scrittura)

Come regola generale, una chiave primaria non dovrebbe avere alcun significato per l'azienda. OSSIA un SSN non deve essere utilizzato come chiave primaria. La ragione di ciò è che le regole aziendali cambiano. Un valore che è "sempre" unico come un SSN di solito ha un'eccezione che viene scoperta lungo la strada ci sono solo un miliardo di possibili combinazioni di 9 cifre ci sono 300 milioni di cittadini in America.

Allo stesso modo anche i valori che saranno "sempre" presenti hanno delle eccezioni. Il nostro sistema di gestione delle risorse umane deve supportare aziende al di fuori dell'America in cui i dipendenti non dispongono di un numero di previdenza sociale

Lo scopo di una chiave primaria è di dare un identificatore immutabile a un'entità in modo che io sappia sempre dove trovare quell'entità. Le chiavi non primarie o gli indici vengono utilizzati per consentire a un utente di cercare facilmente un'entità in possesso della conoscenza di tale attributo.

Pensa alla chiave primaria come a un indirizzo di un blocco di memoria. Se un'altra entità detiene tale indirizzo, ci aspettiamo di essere in grado di passare a quell'indirizzo e trovare l'entità originale.

Modifica Non tendo a scrivere risposte qui senza sapere di cosa sto parlando. Conosco la differenza tra un surrogato e una chiave primaria. Se vuoi una seconda opinione. Qui vai

You have three choices for defining the primary key for most tables: a 'natural' key, a >system generated or 'internal' key, and (for smaller lookup tables) a mnemonic code.

Internal ID numbers

Rule of thumb: for any table over 100 records, use a system-generated number as the primary key.

Any time your system stores a record, particularly when the record's 'natural' key ID number is controlled by an outside source, there is a risk that the outside source may do odd things with its data - it may re-use the ID number, it may update a record by changing the ID, or the system may have a problem talking to the outside source and may get the same record sent twice. In every such case, your system must not be tightly coupled to the outside source - where "tightly coupled" means that a problem in the outside source system causes your system to fail to work correctly.

Therefore, as a form of humility, your system should accept at face value whatever the outside sources send. The system should not try to fix other systems, nor modify their data, nor maintain referential integrity for them. (Your system should do all of those things for its own internally created data, but not for other systems' data that is imported.)

When other systems do make corrections to earlier errors, your system should take the later data at face value and overwrite any earlier records they sent. In every case, your system should faithfully and accurately record whatever is sent from other systems.

This approach can only be made to work with system generated ID numbers. Other keys could be created instead, such as the external system's primary key plus (say) the date, but this merely tries to obtain the benefits of humility without acknowledging the work, and exposes your system to problems if any of the above mentioned problems were to occur within the course of a single day (when the dates would be duplicated).

(On an earlier system I tried the external-key-plus-datetime approach, only to find that duplicates arrived within the same datetime down to the second -- one second being the finest granularity of an Oracle datetime.)

Note: this does NOT mean your system allows users to enter bad data. Data that users enter directly in the system is part of the system's own data, and therefore it takes responsibility for that data's cleanliness and accuracy. All data entry screens will provide and enforce "edits" to ensure the correctness of data entered by users directly into your system.

Natural Keys

Rule of thumb: don't use natural keys as primary keys; instead, store them as attributes.

I view 'natural' keys as actually just the keys of an external system -- even if that external system is the outside world. Generally, natural keys are untrustworthy in the extreme. Here are some examples of their untrustworthy behavior:

A criminal steals someone's (US Government generated) Social Security Number (SSN) to commit fraud (now two people have one SSN). Later, the victim is assigned a new SSN (now one person has had two SSNs at different times.) A system that used SSNs as a 'natural' key would have a terrible time with this scenario. A visitor to the US (or an American child) who has no SSN cannot be tracked by the system, and essentially doesn't exist.

A supplier re-uses SKUs (Stock Keeping Units), i.e. when a product becomes obsolete its SKU is reassigned to a new product.

You'll still want to store the supplier SKU -- you just need to be alert to its potential for misbehavior.

Mnemonic Codes

Rule of thumb: for any table over 100 records, use a system-generated number as the primary key.

For lookup tables, I like to take advantage of the fact that primary key values of lookup tables become foreign key values in the tables that reference them -- and if a code is mnemonic (its meaning is easily remembered) then often the user doesn't even need to do a join to know what the key means.