NULL permesso in chiave primaria – perché e in quale DBMS?

Oltre alla mia domanda “Perché usare’not null primary key ‘in TSQL?” …

Come ho capito dalle altre discussioni, alcuni RDBMS (ad esempio SQLite, MySQL ) autorizza “NULL” univoco nella chiave primaria.

Perché è permesso e come potrebbe essere utile?

Background: Credo che sia vantaggioso per la comunicazione con colleghi e professionisti del database conoscere le differenze nei concetti fondamentali, negli approcci e nelle loro implementazioni in diversi DBMS.

Gli appunti

  • MySQL viene riabilitato e restituito all’elenco “NOT NULL PK”.
  • SQLite è stato aggiunto (grazie a Paul Hadfield) all’elenco “NULL PK”:

Ai fini della determinazione dell’univocità dei valori delle chiavi primarie, i valori NULL sono considerati distinti da tutti gli altri valori, inclusi altri valori NULL.

    Se un’istruzione INSERT o UPDATE tenta di modificare il contenuto della tabella in modo che due o più righe presentino valori di chiave primaria identici, si tratta di una violazione del vincolo. Secondo lo standard SQL, PRIMARY KEY dovrebbe sempre indicare NOT NULL. Sfortunatamente, a causa di una supervisione di codifica di vecchia data, questo non è il caso di SQLite.

    A meno che la colonna non sia INTEGER PRIMARY KEY SQLite consente i valori NULL in una colonna PRIMARY KEY . Potremmo cambiare SQLite per conformarci allo standard (e potremmo farlo in futuro), ma nel momento in cui la svista è stata scoperta, SQLite era in così ampio uso che temevamo di rompere il codice legacy se riparassimo il problema.

    Quindi per ora abbiamo scelto di continuare a consentire i NULL nelle colonne PRIMARY KEY. Gli sviluppatori dovrebbero essere consapevoli, tuttavia, che potremmo cambiare SQLite per conformarci allo standard SQL in futuro e che dovremmo progettare di conseguenza i nuovi programmi.

    – SQL come inteso da SQLite: CREATE TABLE

    Supponiamo di avere una chiave primaria contenente una colonna Nullabile Kn.

    Se si desidera che una seconda riga venga rifiutata per il fatto che in quella seconda riga, Kn è nullo e la tabella contiene già una riga con Kn null, allora si richiede effettivamente che il sistema tratti il ​​confronto “row1.Kn = row2 .Kn “come se avessi dato VERO (perché in qualche modo vuoi che il sistema rilevi che i valori chiave in quelle file sono effettivamente uguali). Tuttavia, questo confronto si riduce al confronto “null = null” e lo standard specifica esplicitamente che null non è uguale a nulla, incluso se stesso.

    Consentire quello che vuoi, equivarrebbe quindi a SQL deviando dai propri principi riguardanti il ​​trattamento di null. Ci sono innumerevoli incongruenze in SQL, ma questo particolare non ha mai superato il comitato.

    Non so se le versioni precedenti di MySQL differiscono su questo, ma a partire dalle versioni moderne una chiave primaria deve essere su colonne che non sono nulle. Vedere la pagina di manuale su CREATE TABLE : “A PRIMARY KEY è un indice univoco in cui tutte le colonne chiave devono essere definite come NOT NULL . Se non vengono esplicitamente dichiarate come NOT NULL , MySQL le dichiara in modo implicito (e silenzioso).”

    Per quanto riguarda la teoria dei database relazionali:

    • La chiave primaria di una tabella viene utilizzata per identificare in modo univoco ogni singola riga nella tabella
    • Un valore NULL in una colonna indica che non si conosce quale sia il valore
    • Pertanto, non si dovrebbe mai utilizzare il valore di “Non so” per identificare in modo univoco una riga in una tabella.

    A seconda dei dati che si stanno modellando, è ansible utilizzare un valore “composto” anziché NULL. Ho usato 0, “N / A”, “1 gen. 1980” e valori simili per rappresentare i dati fittizi “noti per essere mancanti”.

    La maggior parte, se non tutti, i motori DB consentono un vincolo UNIQUE o un indice, che consente valori di colonna NULL, sebbene (idealmente) solo una riga possa essere assegnata al valore null (altrimenti non sarebbe un valore univoco). Questo può essere usato per supportare le situazioni irritantemente pragmatiche (ma occasionalmente necessarie) che non si adattano perfettamente alla teoria relazionale.

    Bene, potrebbe permetterti di implementare il Null Object Pattern in modo nativo all’interno del database. Quindi, se si stesse usando qualcosa di simile nel codice, che interagiva molto da vicino con il DB, si poteva semplicemente cercare l’object corrispondente alla chiave senza dover ricorrere al controllo di null.

    Ora, se questa funzionalità è utile, non ne sono sicuro, ma in realtà è una questione se i professionisti di disabilitare le pacchette null in tutti i casi superano in assoluto gli ostacoli che impediscono a qualcuno che (nel bene o nel male) voglia effettivamente utilizzare chiavi null. Ciò varrebbe la pena solo se fosse ansible dimostrare alcuni miglioramenti non banali (come una ricerca più rapida della chiave) dall’assicurare che le chiavi non siano nulle. Alcuni motori DB mostrerebbero questo, altri potrebbero non farlo. E se non ci sono veri professionisti a forzare questo, perché limitare artificialmente i tuoi clienti?

    Come discusso in altre risposte, NULL intendeva dire “l’informazione che dovrebbe andare in questa colonna è sconosciuta”. Tuttavia, è anche usato frequentemente per indicare un significato alternativo di “questo attributo non esiste”. Questa è un’interpretazione particolarmente utile quando si osservano i campi di data e ora che vengono interpretati come il momento in cui si è verificato un determinato evento, nel qual caso viene spesso utilizzato NULL per indicare che l’evento non si è ancora verificato.

    È un problema che SQL non supporta questa interpretazione molto bene – perché funzioni correttamente, ha davvero bisogno di avere un valore separato (qualcosa come “mai”) ​​che non si comporta come null (“mai” dovrebbe essere uguale a “mai” e deve essere confrontato come superiore a tutti gli altri valori). Ma siccome SQL non ha questa nozione, e non c’è un modo conveniente per aggiungerlo, usare null a questo scopo è spesso la scelta migliore.

    Questo lascia il problema che quando un timestamp di un evento che potrebbe non essersi verificato dovrebbe essere parte della chiave primaria di una tabella (un requisito comune potrebbe essere l’uso di una chiave naturale insieme a un timestamp di eliminazione quando si utilizza la cancellazione morbida con un requisito per la possibilità di ricreare l’object dopo la cancellazione) si desidera veramente che la chiave primaria abbia una colonna nullable. Ahimè, questo non è permesso nella maggior parte dei database, e invece si deve ricorrere a una chiave primaria artificiale (ad esempio un numero di sequenza di righe) e un vincolo UNIQUE per quella che altrimenti avrebbe dovuto essere la propria chiave primaria.

    Uno scenario di esempio, per chiarire questo: ho una tabella users . Poiché richiedo a ciascun utente di avere un nome utente distinto, decido di utilizzare il username come chiave primaria. Voglio supportare la cancellazione degli utenti, ma poiché ho bisogno di rintracciare l’esistenza degli utenti storicamente a fini di controllo, utilizzo l’eliminazione soft (nella prima versione dello schema, aggiungo un flag “eliminato” all’utente e assicuriamo che l’eliminazione flag è selezionato in tutte le query in cui sono previsti solo utenti attivi).

    Un ulteriore requisito, tuttavia, è che se un nome utente viene eliminato, dovrebbe essere disponibile per la registrazione di nuovi utenti. Un modo interessante per ottenere ciò sarebbe quello di far cambiare il flag cancellato in un timestamp nullable (dove i null indicano che l’utente non è stato cancellato) e metterlo nella chiave primaria. Sono state le chiavi primarie per consentire colonne nullable, questo avrebbe il seguente effetto:

    • La creazione di un nuovo utente con un nome utente esistente quando la colonna deleted di quell’utente è nullo verrebbe negata come una voce di chiave duplicata
    • L’eliminazione di un utente cambia la sua chiave (che richiede modifiche a cascata per le chiavi esterne che fanno riferimento all’utente, che è subottimale ma se le eliminazioni sono rare è accettabile) in modo che la colonna deleted sia un timestamp per quando è avvenuta l’eliminazione
    • Ora un nuovo utente (che avrebbe un timestamp deleted nullo) può essere creato con successo.

    Tuttavia, questo non può essere effettivamente raggiunto con SQL standard, quindi è necessario utilizzare una chiave primaria diversa (probabilmente un ID utente numerico generato in questo caso) e utilizzare un vincolo UNIQUE per rafforzare l’unicità di ( username , deleted ).

    Avere la chiave primaria null può essere utile in alcuni scenari. In uno dei miei progetti ho usato questa funzione durante la sincronizzazione dei database: una sul server e molti su diversi dispositivi degli utenti. Considerando il fatto che non tutti gli utenti hanno accesso a Internet tutto il tempo, ho deciso che solo il database principale sarà in grado di fornire id alle mie entity framework. SQLite ha il proprio meccanismo per numerare le righe. Se avessi usato un campo id aggiuntivo, avrei usato più banda. Avere null as id non solo mi informa che un’entity framework è stata creata sul dispositivo client quando non ha avuto accesso a Internet, ma riduce anche la complessità del codice. L’unico inconveniente è che sul dispositivo client non riesco a ottenere un’ quadro dal suo id, a meno che non sia stato precedentemente sincronizzato con il database principale. Tuttavia questo non è un problema dal momento che il mio utente si prende cura delle quadro per i loro parametri, non il loro ID univoco.