sql integrità - Una chiave esterna può essere NULL e / o duplicata?




5 Answers

Risposta breve: Sì, può essere NULL o duplicato.

Voglio spiegare perché una chiave esterna potrebbe aver bisogno di essere nulla o potrebbe essere univoco o non unico. Prima di tutto ricorda che una chiave esterna richiede semplicemente che il valore in quel campo esista prima in una tabella diversa (la tabella padre). Questo è tutto un FK è per definizione. Null per definizione non è un valore. Null significa che non sappiamo ancora quale sia il valore.

Lascia che ti dia un esempio di vita reale. Supponiamo di avere un database che memorizza le proposte di vendita. Supponiamo inoltre che ogni proposta contenga solo un addetto alle vendite e un cliente. Pertanto la tabella delle proposte avrebbe due chiavi esterne, una con l'ID cliente e una con l'ID rappresentante vendite. Tuttavia, al momento della creazione del record, un rappresentante di vendita non viene sempre assegnato (poiché nessuno è ancora libero di lavorarci), quindi l'ID cliente viene compilato ma l'ID rappresentante vendite potrebbe essere nullo. In altre parole, in genere è necessario avere un FK null quando non si può conoscere il suo valore al momento dell'inserimento dei dati, ma si conoscono altri valori nella tabella che devono essere immessi. Per consentire i null in un FK in generale tutto ciò che dovete fare è consentire i null sul campo che ha l'FK. Il valore nullo è separato dall'idea che si tratti di un FK.

Che sia univoco o non unico, si riferisce al fatto che la tabella abbia una relazione one-one o one-many con la tabella padre. Ora se hai una relazione one-one, è possibile che tu possa avere i dati tutti in una tabella, ma se la tabella sta diventando troppo ampia o se i dati sono su un argomento diverso (il dipendente - esempio di assicurazione @tbone ha dato per esempio), quindi vuoi tabelle separate con un FK. Dovresti quindi rendere questo FK anche il PK (che garantisce l'unicità) o porre un vincolo univoco su di esso.

La maggior parte dei FK ha una relazione uno a molti e questo è ciò che ottieni da un FK senza aggiungere un ulteriore vincolo sul campo. Quindi, ad esempio, hai una tabella degli ordini e la tabella dei dettagli dell'ordine. Se il cliente ordina dieci articoli alla volta, ha un unico ordine e dieci record di dettaglio degli ordini che contengono lo stesso orderID dell'FK.

referenziale primaria

Per favore chiarisci due cose per me:

  1. Una chiave esterna può essere NULL?
  2. Una chiave esterna può essere duplicata?

Per quanto io sappia, NULL non dovrebbe essere usato in chiavi esterne, ma in alcune mie applicazioni sono in grado di inserire NULL sia in Oracle che in SQL Server, e non so perché.




Dalla bocca del cavallo:

Le chiavi esterne consentono valori chiave che sono tutti NULL, anche se non ci sono chiavi PRIMARY o UNIQUE corrispondenti

Nessun vincolo sulla chiave esterna

Quando non sono definiti altri vincoli sulla chiave esterna, qualsiasi numero di righe nella tabella figlio può fare riferimento allo stesso valore della chiave genitore. Questo modello consente i null nella chiave esterna. ...

Vincolo NOT NULL sulla chiave esterna

Quando i valori nulli non sono consentiti in una chiave esterna, ogni riga della tabella figlio deve fare esplicito riferimento a un valore nella chiave parent perché i null non sono consentiti nella chiave esterna.

Qualsiasi numero di righe nella tabella figlio può fare riferimento allo stesso valore della chiave genitore, quindi questo modello stabilisce una relazione uno-a-molti tra la madre e le chiavi esterne. Tuttavia, ogni riga nella tabella figlio deve avere un riferimento a un valore della chiave genitore; l'assenza di un valore (un nullo) nella chiave esterna non è consentita. Lo stesso esempio nella sezione precedente può essere utilizzato per illustrare tale relazione. Tuttavia, in questo caso, i dipendenti devono avere un riferimento a un reparto specifico.

Vincolo UNICO sulla chiave esterna

Quando un vincolo UNIQUE è definito sulla chiave esterna, solo una riga nella tabella figlio può fare riferimento a un determinato valore della chiave genitore. Questo modello consente i null nella chiave esterna.

Questo modello stabilisce una relazione uno-a-uno tra la madre e le chiavi esterne che consente valori indeterminati (null) nella chiave esterna. Ad esempio, si supponga che la tabella dei dipendenti abbia una colonna denominata MEMBERNO, che fa riferimento a un numero di membro del dipendente nel piano di assicurazione aziendale. Inoltre, una tabella denominata INSURANCE ha una chiave primaria denominata MEMBERNO e altre colonne della tabella mantengono le rispettive informazioni relative a una polizza assicurativa dipendente. Il MEMBERNO nella tabella dei dipendenti deve essere sia una chiave esterna che una chiave univoca:

  • Per applicare le regole di integrità referenziale tra le tabelle EMP_TAB e INSURANCE (il vincolo FOREIGN KEY)

  • Per garantire che ogni dipendente abbia un numero di iscrizione univoco (il vincolo di chiave UNIQUE)

Vincoli UNICI e NON NULL sulla chiave esterna

Quando entrambi i vincoli UNIQUE e NOT NULL sono definiti sulla chiave esterna, solo una riga nella tabella figlio può fare riferimento a un dato valore della chiave padre e poiché i valori NULL non sono consentiti nella chiave esterna, ogni riga nella tabella figlio deve fare esplicitamente riferimento un valore nella chiave genitore.

Guarda questo:

Oracle 11g link




Ecco un esempio che utilizza la sintassi di Oracle:
Per prima cosa creiamo una tabella COUNTRY

CREATE TABLE TBL_COUNTRY ( COUNTRY_ID VARCHAR2 (50) NOT NULL ) ;
ALTER TABLE TBL_COUNTRY ADD CONSTRAINT COUNTRY_PK PRIMARY KEY ( COUNTRY_ID ) ;

Crea la PROVINCIA della tabella

CREATE TABLE TBL_PROVINCE(
PROVINCE_ID VARCHAR2 (50) NOT NULL ,
COUNTRY_ID  VARCHAR2 (50)
);
ALTER TABLE TBL_PROVINCE ADD CONSTRAINT PROVINCE_PK PRIMARY KEY ( PROVINCE_ID ) ;
ALTER TABLE TBL_PROVINCE ADD CONSTRAINT PROVINCE_COUNTRY_FK FOREIGN KEY ( COUNTRY_ID ) REFERENCES TBL_COUNTRY ( COUNTRY_ID ) ;

Questo funziona perfettamente bene in Oracle. Si noti che la chiave esterna COUNTRY_ID nella seconda tabella non ha "NOT NULL".

Ora per inserire una riga nella tabella PROVINCE, è sufficiente specificare solo PROVINCE_ID. Tuttavia, se hai scelto di specificare anche COUNTRY_ID, deve esistere già nella tabella COUNTRY.




In parole povere, le relazioni "non identificative" tra entità sono parte di ER-Model ed è disponibile in Microsoft Visio durante la progettazione di ER-Diagram. Questo è necessario per applicare la cardinalità tra entità di tipo "zero o più di zero" o "zero o uno". Nota questo "zero" in cardinalità invece di "uno" in "uno a molti".

Ora, un esempio di relazione non identificante in cui la cardinalità può essere "zero" (non identificante) è quando diciamo un record / oggetto in un'entità: A "può" o "potrebbe non" avere un valore come riferimento al record / s in un'altra Entità-B.

Come, c'è una possibilità per una registrazione dell'entità-A di identificarsi nei record di un'altra Entità-B, quindi dovrebbe esserci una colonna in Entità-B per avere il valore di identità della registrazione di Entità-B. Questa colonna può essere "Null" se nessun record in Entity-A identifica il / i record (o, oggetto / i) in Entity-B.

Nel paradigma Object Oriented (nel mondo reale), ci sono situazioni in cui un oggetto di Class-B non dipende necessariamente (fortemente accoppiato) sull'oggetto di classe A per la sua esistenza, il che significa che la Classe B è liberamente accoppiata con Class- Un tale che la Classe A possa "Contenere" (Contenimento) un oggetto di Classe-A, in contrapposizione al concetto di oggetto di Classe B deve avere (Composizione) un oggetto di Classe-A, per suo (oggetto di classe- B) creazione.

Dal punto di vista Query SQL, è possibile interrogare tutti i record nell'entità-B che sono "non null" per la chiave esterna riservata per Entity-B. Ciò porterà tutti i record con un determinato valore corrispondente per le righe nell'entità-A, in alternativa tutti i record con valore Null saranno i record che non hanno alcun record nell'entità-A nell'entità-B.




Penso che la chiave esterna di una tabella sia anche la chiave primaria per qualche altra tabella. Quindi non consentirà null. Quindi non si tratta di avere un valore nullo nella chiave esterna.




Related

sql sql-server oracle foreign-keys