Vorrei capire gli indici e le primary key

Ciao a tutti ragazzi

potete suggerirmi una guida/documento che mi faccia capire praticamente a cosa servono in concreto gli index e le primary key?

vorrei capire soprattutto con dei database con dei dati veri, non solo accennati. In modo da capire veramente.

grazie a tutti

potete suggerirmi una guida/documento che mi faccia capire praticamente a cosa servono in concreto gli index e le primary key?

Le Primary Key sono dei campi che identificano univocamente un record all'interno di una tabella.
E' in uso usare un identificativo univoco numerico ed autoincrementale per ottenere questo risultato, ma non sempre è così e non sempre è la scelta migliore.
Prova a pensare ad una tabella contenente dei dati anagrafici, potresti identificare univocamente un individuo (un record) impostando in ID per ogni inserimento, ma potresti anche prendere in considerazione il fatto che il Codice Fiscale, ad esempio, identifica una ed una sola persona nella vita reale, e quindi potresti replicare questa situazione scegliendo questo campo com Chiave Primaria.
Questo ti spiega già come mai un campo chiave non può essere vuoto o NULL.
Normalmente la PK (Primary Key) è impostata su un campo, ma puoi benissimo decidere di utilizzare due o più campi (si parla allora di chiave composta).
Sempre facendo, scolasticamente parlando, riferimento ad una tabella con anagrafiche potresti impostare una chiave su due campi, ad esempio nome e data di nascita (lo so che ci possono essere due persone che hanno lo stesso nome e sono nate nello stesso giorno, ma è pur sempre un esempio.), quindi nella tabella ci potranno essere nominativi ripetuti, date di nascita ripetute, ma non ci potranno essere due record con lo stesso nominativo e la stessa data di nascita, proprio perchè la coppia identifica il singolo record. Uno ed uno solo.

Per quanto riguarda glli indici, provo a spiegerti con un esempio.
Immagina la tua tabella come una biblioteca. Quando viene inaugurata, ci saranno tutti i libri sistemati con un certo criterio, in un numero magari non troppo elevato, e quinci cercare un volume non sarà difficile.
Man mano che la biblioteca si ingramdisce e le esigenze portano a modifciare l'ordine dei libri, cercare un singolo libro inizia ad essere una operazione complicata.
Ecco perchè si ricorre a degli schedari che raccolgono la posizione di ogni singolo testo all'interno della biblioteca.
Gli indici sono più o meno la stessa cosa.
Se devi cercare un record all'interno di una tabella piccola, puoi anche scorrerla tutta fino a trovare il record interessato, ma se hai a che fare con tabelle grandi con centinaia di migliaia di record, questo metodo diviene scomodo, lento e poco pratico. L'uso degli indici (schedari) ci aiuta nella ricerca velocizzandola ed evitando di farci girare per tutti gli scaffali della biblioteca in cerca del mio libro.

vorrei capire soprattutto con dei database con dei dati veri, non solo accennati. In modo da capire veramente.

Con 1, 100, 1000, o 1.000.000 di record il discroso è immutato. Una tabella contenente il nome e cognome dei miei 20 compagni di classe, non è un database vero?

Grazie Vplaza che mi rispondi sempre...grazie grazie



ma io con l'indice e con la PK che devo fare ?

andiamo sul pratico

1)
vado a definire una tabella amici con tutti i nomi,cognomi e telefono dei miei ventimila amici.e definisco id come Primary Key

CREATE TABLE amici (
  id INT(20) AUTO_INCREMENT,
  nome VARCHAR(20),
  cognome VARCHAR(20),
  telefono INT(10),
  PRIMARY KEY(id)
);

ora così dopo creato questa tabella amici, vado ad inserire tutti i dati dei miei ventimila amici con INSERT........
quindi ottengo una tabella amici piena di dati e quindi con 20.000 righe (record , giusto? con record identifichi le righe della tabella,giusto?)

ora con la PK, io che faccio?


2)
Ora poi mi creo una tabella amici2 come prima, ma ha anche un indice col nome indice_cognomi; quindi ho indicizzato il cognome.

CREATE TABLE amici2 (
  id INT(20) AUTO_INCREMENT,
  nome VARCHAR(20),
  cognome VARCHAR(20),
  telefono INT(10),
  PRIMARY KEY(id),
  INDEX indice_cognomi (cognome)
);

Vado ad inserire tutti i 20000 amici che ho, come ho fatto prima.

Ora ottengo quindi una tabella amici2 piena di dati dei miei amici.

Cosa faccio con l'indice?

[alemoppo]

ora con la PK, io che faccio?

Ammettiamo ora che tu voglia salvare i voti di matematica di tutti i tuoi amici. Come potresti fare?

Forse ti verrebbe in mente una cosa del genere:

CREATE TABLE votiMatematica1 (
  cognome VARCHAR(20),
  voto TINYINT
);

Però ogni volta in ogni riga di voto dovrai salvarti il cognome di ogni tuo amico per sapere la riga di voto a chi appartiene (assumendo cognomi univoci).
Ecco quindi che serve un elemento che identifichi univocamente ogni tuo singolo amico, solitamente l'id. Potrai fare:

CREATE TABLE votiMatematica2 (
  id_amico INT(20),
  voto TINYINT,
  FOREIGN KEY (id_amico) REFERENCES amici(id)
);

Con:

FOREIGN KEY (id_amico) REFERENCES amici(id)

dici a mysql che id_amico è collegato ad una riga del tuo amico salvato nella tabella amici (in particolare all'id: ovviamente questi due dati devono essere dello stesso tipo!). Addirittura puoi anche dire a mysql di eliminare tutti i voti di un tuo amico in automatico se dovessi eliminarlo dalla tua tabella (con ON DELETE..).

Cosa faccio con l'indice?

Stessa cosa per l'index cognome della tabella amici2. Però tieni presente che di solito l'elemento che occupa meno è un numero e non una stringa: la tabella "votiMatematica2" non occuperà chissà tanto perché contiene di fatto 2 numeri. La tabella votiMatematica1 invece occuperà enormemente di più perché invece di un numero c'è una stringa che sta a identificare un amico. Se hai tantissimi record, pensa che risparmio di spazio!

EDIT:
Per spiegarmi meglio ti faccio un esempio:

Amici:

id	nome	cognome	telefono
1	Luca	Bianchi	+39123456789
2	Andrea	Rossi	+39987654321

votiMatematica2

id_amico	voto
1	4
2	8
1	3
1	4
2	9
1	2
2	8
2	9
1	4

votiMatematica1 (che spreco di spazio!)

cognome	voto
'Bianchi'	4
'Rossi'	8
'Bianchi'	3
'Bianchi'	4
'Rossi'	9
'Bianchi'	2
'Rossi'	8
'Rossi'	9
'Bianchi'	4

Come puoi vedere, Luca non è proprio portato per la matematica. Andrea invece sì.

Ciao!

Grazie Alemoppo per la tua risposta,
scusa ma sto all'inizio e vorrei capire bene cosa significa questo argomento.


con questa query

FOREIGN KEY (id_amico) REFERENCES amici(id)

dico che sono collegati i due campi delle due tabelle
cosa succede quindi? che mi facilito? il momento di inserire i dati nella seconda tabella?

[alemoppo]

Quando salvi i dati, mysql non sa comunque a chi deve assegnare il voto che stai inserendo: devi comunque inserire l'id (del tuo amico) e il voto.

Ti viene in contro tantissimo come ho detto sopra se tu volessi eliminare un amico: se imposti ON DELETE CASCADE, alla sua eliminazione verranno eliminati anche tutti i record a lui collegati (nel tuo caso, tutti i suoi voti).
Oppure puoi impostare che quando modifichi il suo id, questo viene modificato in automatico anche a tutte le righe ad esso collegate (nel tuo caso, a tutti i record voti di quell'amico).

A parte i casi sopra (e pochi altri), all'inizio ti risulterà abbastanza inutile come cosa. Prendi però la buona abitudine di collegare sempre i dati. Poi magari quando sarai più esperto potrai valutare tu stesso se referenziare o meno, ma per ora ti consiglio di farlo sempre.

Ciao!

Alemoppo grazie per la tua risposta, ma io non sono sicuro di aver capito.

tu hai usato una

FOREIGN KEY (id_amico) REFERENCES amici(id)

io invece una Primary key.

Da una guida che ho trovato ho letto che:

in un database relazionale le tabelle sono collegate tra loro tramite relazioni logiche, facendo si che righe di una tabella contengano riferimenti a valori chiave di un'altra tabella.

I principali aspetti dei punti di legame tra la cartella principale amici e quella secondaria votiMatematica2 sono:

- i campi coinvolti nella relazione devono essere di tipi simili.
- le tabelle devohno avere lo stesso Storage Engine
- la FOREIGN KEY deve essere contenuta nella tabella secondaria
- i campi coinvolti nella relazione devono essere sottoposti ad un indice di qualche tipo , sia nella tabella principale che in quella secondaria in modo da renderli accessibili in modo efficiente

1)per quest'ultima caratteristica l'indice di cui si parla nella tabella principale sarebbe la PK?


La mia tabella amici è questa

CREATE TABLE amici (
  id INT(20) AUTO_INCREMENT,
  nome VARCHAR(20),
  cognome VARCHAR(20),
  telefono INT(10),
  PRIMARY KEY(id)
);

voglio ora salvare i voti di matematica dei miei amici e perciò ho modificato secondo la guida la tua tabella:

CREATE TABLE votiMatematica2 (
  id_amico INT(20),
  voto TINYINT,
  index id_amico_ind(id_amico),
  FOREIGN KEY (id_amico) REFERENCES amici(id),
);

2)Cosa è una Foreign KEY ? ha legato id_amico della tabella votiMatematica2 con id della tabella amici?

3)cosa fa l'indice? tramite id_amico, che si ricava da id (grazie alla Foreign Key) si ha il voto in matematica dell'amico?


4)ma quindi mettendo insieme le cose che mi hai detto mi viene da dire: è solo un modo di costruire le tabelle che devono essere legate tra loro?

per memorizzare i dati all'interno delle due tabelle utilizzo comunque questi due comandi per le due tabelle

INSERT INTO amici (id,nome,cognome,telefono) VALUES ('1','Mario', 'Rossi', '12345689');

INSERT INTO votiMatematica2 (id_amico,voto) VALUES ('1','7');

per leggere i dati cmq utilizzo due comandi diversi (per la tabella amici e per la tabella votiMatematica2)

5)che poi quando vai ad eliminare un campo si eliminano tutti i vari campi legati ad esso, è una conseguenza del fatto che si è costruito così il database, giusto?

[alemoppo]

per quest'ultima caratteristica l'indice di cui si parla nella tabella principale sarebbe la PK?

Sì, ovvero un campo che lega due elementi di due tabelle differenti.

Cosa è una Foreign KEY ? ha legato id_amico della tabella votiMatematica2 con id della tabella amici?

Sì.

cosa fa l'indice? tramite id_amico, che si ricava da id (grazie alla Foreign Key) si ha il voto in matematica dell'amico?

In che senso "che si ricava da id"? L'indice dice semplicemente a che amico è riferito il voto che contiene quella riga. Io ho usato un campo id ma è la stessa cosa

ma quindi mettendo insieme le cose che mi hai detto mi viene da dire: è solo un modo di costruire le tabelle che devono essere legate tra loro?

Esattamente, tieni presente che questi database si chiamano "database relazionali", non è una cosa da poco come ti sembra ora.

per leggere i dati cmq utilizzo due comandi diversi (per la tabella amici e per la tabella votiMatematica2)

In realtà esistono le query JOIN. Servono per estrarre in una volta sola (ad esempio) tutti i tuoi amici con i loro rispettivi voti in matematica. Sarà una cosa del genere:

SELECT * FROM amici, votiMatematica2 WHERE amici.id = votiMatematica2.id_amico

Ovvero seleziona tutto dalle due tabelle con la condizione che il campo id della tabella amici sia uguale al campo id_amico della tabella votiMatematica2 (esempio riferito alle tabelle del mio messaggio precedente). La stessa cosa si sarebbe ottenuta con una JOIN. Inoltre le JOIN permettono di fare anche altro ma non è un argomento semplicissimo da spiegare qui, dovresti leggere una guida come vedo stai già facendo, bravo!

che poi quando vai ad eliminare un campo si eliminano tutti i vari campi legati ad esso, è una conseguenza del fatto che si è costruito così il database, giusto?

Sì, ma devi specificarlo durante la creazione delle tabelle con ON DELETE CASCADE. (vedi tutte le altre possibilità nel link che ti ho lasciato sopra, questa è solo una delle tante).

--------

Ritornando alla tua domanda iniziale, è comunque utilissimo avere una colonna con un campo univoco che rappresenta le singole linee: se tu volessi eliminare una singola riga, come faresti senza un campo di cui sei sicuro non ci siano duplicati? Inoltre utilizzare numeri invece di stringhe per collegare due tabelle credo sia molto più ottimizzato e veloce (ad esempio nelle SELECT) rispetto confrontare due stringhe!

Ciao!

CREATE TABLE votiMatematica2 (
  id_amico INT(20),
  voto TINYINT,
  index id_amico_ind(id_amico),
  FOREIGN KEY (id_amico) REFERENCES amici(id),
);

il mio indice si chiama " id_amico_ind " ed all'interno delle parentesi ha id_amico, cioè la chiave esterna che è collegata all'id della tabella amici.

quando ti ho chiesto cosa fa l'indice, mi riferivo al fatto che volevo sapere a quale passaggi meccanici/informatici fa per avere questo effetto che mi hai detto:

L'indice dice semplicemente a che amico è riferito il voto che contiene quella riga

Perchè ad un id_amico corrisponde un voto proprio per come sono state costruite le tabelle, giusto?

Ritornando alla tua domanda iniziale, è comunque utilissimo avere una colonna con un campo univoco che rappresenta le singole linee: se tu volessi eliminare una singola riga, come faresti senza un campo di cui sei sicuro non ci siano duplicati?

scusami, ma c'è un giro di parole non indifferenti nella mia testa.
quando fai quest'ultima osservazione a che ti riferisci?

le PK, permettono di avere una colonna con un campo univoco ? proprio perchè si va a definire nella tabella che la PK sia AUTO_INCREMENT?

se è così, per l'indice non si va a definire id_amico AUTO_INCREMENT proprio per via del fatto che si è andata a definire una FOREIGN KEY che è legata appunto ad id che è AUTO_INCREMENT?




Per le JOIN, aspetterei di capire bene questo .....che a quanto mi sembra di capire, ci sto girando intorno alla soluzione....

[alemoppo]

Per quanto riguarda gli indici, è tutto spiegato bene qui (io sinceramente non li uso granché).

le PK, permettono di avere una colonna con un campo univoco ? proprio perchè si va a definire nella tabella che la PK sia AUTO_INCREMENT?

Circa: un campo PRIMARY KEY non necessariamente è AUTO_INCREMENT.
Già il fatto di essere PRIMARY KEY implica che il campo non può contenere doppioni; in altre parole significa che non possono esserci più righe con lo stesso contenuto in quella colonna (ricevi un errore se tenti di inserire un valore già presente in un'altra riga).
AUTO_INCREMENT significa semplicemente che il valore non lo devi specificare tu durante la INSERT ma lo inserisce lui incrementando l'ultimo valore inserito.

se è così, per l'indice non si va a definire id_amico AUTO_INCREMENT proprio per via del fatto che si è andata a definire una FOREIGN KEY che è legata appunto ad id che è AUTO_INCREMENT?

id_amico non va definito AUTO_INCREMENT perché possono esserci doppioni e comunque devono essere riferiti ad amici.id. Guarda l'esempio sopra che ti ho fatto, lascia stare per ora l'uso degli indici: questi servono solo per ottimizzare le ricerche.

Ciao!

Per quanto riguarda gli indici, è tutto spiegato bene qui (io sinceramente non li uso granché).

io da li sto studiando è solo che manca la parte del dopo creazione....però forse leggendo la normalizzazione del database e quello che hai scritto tu ....forse torna

Circa: un campo PRIMARY KEY non necessariamente è AUTO_INCREMENT.
Già il fatto di essere PRIMARY KEY implica che il campo non può contenere doppioni; in altre parole significa che non possono esserci più righe con lo stesso contenuto in quella colonna (ricevi un errore se tenti di inserire un valore già presente in un'altra riga).
AUTO_INCREMENT significa semplicemente che il valore non lo devi specificare tu durante la INSERT ma lo inserisce lui incrementando l'ultimo valore inserito.

quindi quando inserisco i dati in una tabella come quella mia, in cui id è auto_increment ce lo metto il valore oppure no?

id_amico non va definito AUTO_INCREMENT perché possono esserci doppioni e comunque devono essere riferiti ad amici.id. Guarda l'esempio sopra che ti ho fatto, lascia stare per ora l'uso degli indici: questi servono solo per ottimizzare le ricerche.

Ciao!

id_amico non l'ho definito AUTO_INCREMENT come ho scritto pure io

[alemoppo]

quindi quando inserisco i dati in una tabella come quella mia, in cui id è auto_increment ce lo metto il valore oppure no?

Non devi specificarlo tu, lo inserisce in automatico. Cioè puoi anche "forzarlo" tu (non ricordo, dovresti provare), ma ovviamente è bene lasciar far tutto a lui.

Ciao!

io stacco non ce la faccio più.
Forse sono riuscito a capire, ma è meglio che riprendo domani...Grazie mille spero che col riposo di stasera e stanotte, domani sembra tutto filare alla perfezione, o quasi.

Ti ringrazio moltissimo per la disponibilità

[darbula]

In MYSQL la primary key può essere creata anche senza autoincrement,mentre in altri database relazionali si da per scontato il suo uso.
La foreign key è disponibile invece con motore engine InnoDB che a partire dal 5.5 è impostato di default per le tabelle. (fonte).
Non è che io sia un esperto, ma ogni tanto leggo.
Ricordo hai lettori,qui non basta usare la giusta sintassi SQL per attivare InnoDB ma si deve attivare dal pannello di controllo di altervista.

In MYSQL la primary key può essere creata anche senza autoincrement,mentre in altri database relazionali si da per scontato il suo uso.

Perdonami ma da questa affermazione capisco che non hai ben chiaro cosa sia e a cosa serva la chiave primaria.
Anzi, usare un ID autoincrementale è solo una trovata (definita da alcuni pseudo chiave primaria).
La PK (o chiave primaria o Primary Key) è impostabile su un qualsiasi campo (o anche combinazione di campi) che identifichino univocamente un record. Se leggevi il mio primo reply ti saresti anche accorto che ti facevo l'esempio del codice fiscale. Se questo è univoco, potresti benissimo usare questo campo come PK (e quindi non sarebbe ne numerico ne, a maggior ragione, autoincrementale).

E ti dirò di più, questo discorso vale per qualunque DB. non solo MySQL.