Característiques de les restriccions de claus estrangeres:
A continuació s’expliquen algunes característiques importants de la restricció de claus externes.
- El tipus de dades de la clau externa que s’utilitza a la taula fill ha de ser el mateix que el tipus de dades de la clau principal que s’utilitza a la taula principal per referir-se a la clau externa.
- Qualsevol columna d'índex o diverses columnes només es pot fer referència com a clau externa per a la taula InnoDB.
- Els privilegis de referències o almenys un dels privilegis de les instruccions SELECT, INSERT, UPDATE i DELETE són necessaris per crear una clau externa.
- Es pot crear una clau estrangera de dues maneres. Una mitjançant la sentència CREATE i una altra mitjançant la sentència ALTER.
Requisit previ:
Abans de crear una restricció de clau externa, heu de crear una base de dades i una taula pare amb la clau principal. Suposem que el nom de la base de dades és ' biblioteca I conté dues taules principals anomenades ' llibres 'I' prestatari ’. Feu una connexió amb el servidor MySQL mitjançant mysql client i executeu les sentències SQL següents per crear la base de dades i les taules.
CREAR BASE DE DADES biblioteca;
ÚS biblioteca;
CREAR TAULA llibres(
identificador INT NO NUL AUTO_INCREMENT ,
títol varchar (50) NO NUL ,
autor varchar (50) NO NUL ,
editor varchar (50) NO NUL ,
CLAU PRIMÀRIA (identificador)
) MOTOR = INNODB ;
CREAR TAULA prestataris(
identificador VARCHAR (50) NO NUL ,
nom varchar (50) NO NUL ,
adreça varchar (50) NO NUL ,
correu electrònic varchar (50) NO NUL ,
CLAU PRIMÀRIA (identificador)
) MOTOR = INNODB ;
Definiu la restricció de clau estrangera mitjançant la sentència CREATE
Creeu una taula anomenada ' book_borrow_info ‘Amb restriccions de clau estrangera executant la següent sentència. Aquí, el book_id camp és un clau estrangera per a aquesta taula i tots els valors d 'aquest camp han d' existir al fitxer identificador camp de llibres taula. llibres és la taula pare i book_borrow_info és la taula infantil. Aquí també s’estableixen dues restriccions amb la clau externa. Aquests són ESBORRAR CASCADA i ACTUALITZA CASCADA . Això significa que si alguna clau primària s'eliminarà o s'actualitzarà de la taula principal, s'eliminaran els registres corresponents relacionats amb la taula secundària relacionada amb la clau externa o s'actualitzarà la clau externa.
CREAR TAULA book_borrow_info(
préstec_id VARCHAR (50),
book_id INT ,
data_préstec DATA NO NUL ,
data_retorn DATA NO NUL ,
estat VARCHAR (15) NO NUL ,
ÍNDEX par_ind(book_id),
CLAU PRIMÀRIA (préstec_id,data_préstec),
CLAU ESTRANGERA (book_id) REFERÈNCIES llibres(identificador)
ACTIVAT ESBORRAR CASCADA
ACTIVAT ACTUALITZACIÓ CASCADA
) MOTOR = INNODB ;
Ara, executeu les sentències SQL següents per inserir alguns registres a les dues taules. La primera instrucció INSERT inserirà quatre registres a llibres taula. Els quatre valors de identificador camp de llibres la taula serà 1, 2, 3 i 4 per a l'atribut d'increment automàtic. La segona sentència INSERT inserirà quatre registres a book_borrow_info basat en identificador el valor de llibres taula.
INSERTAR EN llibres VALORS
( NUL , 'Matar un rossinyol', 'Harper Lee', 'Grand Central Publishing'),
( NUL , 'Cent anys de solitud', 'Garcia Marquez', 'Lutfi Ozkok'),
( NUL , 'Un pas a l'Índia', 'Forster, E.M.', 'BBC Hulton Picture Library'),
( NUL , 'Home invisible', 'Ralph Ellison', «Encyclopædia Britannica, Inc.»);
INSERTAR EN book_borrow_info VALORS
('123.490', 1, '2020.02.15', '2020.02.25', 'Tornat'),
('157.643', 2, '2020.03.31', '2020.03.10', 'Pendents'),
('174.562', 4, '2020.04.04', '2020.04.24', 'En préstec'),
('146.788', 3, '2020.04.10', '2020.01.20', 'En préstec');
Si intenteu inserir un valor al camp de clau externa de la taula fill que no existeix al camp de clau principal de la taula pare, MySQL generarà un error. La següent sentència SQL generarà un error perquè la taula pare, llibres no conté cap valor d'identificador 10 .
INSERTAR EN book_borrow_info VALORS('195.684', 10, '2020.04.15', '2020.04.30', 'Tornat');
Després d'executar la següent instrucció DELETE, quan s'eliminarà el quart registre del fitxer llibres taula a continuació, els registres relacionats de book_borrow_info la taula s'eliminarà automàticament per a la restricció de clau externa.
ESBORRAR DE llibres ON identificador= 4;SELECCIONA * des de llibres;
SELECCIONA * des de book_borrow_info;
Definiu la restricció de clau estrangera mitjançant la instrucció ALTER
Al principi, inseriu alguns registres a prestataris i aquesta taula es definirà com a taula principal a la següent EDAT declaració.
INSERTAR EN prestataris VALORS('123.490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH ', '[email protected]'),
('157.643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU ', '[email protected]'),
('174.562', 'John Innes Archie', '55 Main Road LIVERPOOL L2 3OD ', '[email protected]'),
('146.788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME ', '[email protected]');
Executeu el següent EDAT per establir una altra restricció de clau externa per a book_borrow_info taula per establir la relació prestataris taula. Aquí, préstec_id es defineix com una clau estrangera per a book_borrow_info taula.
ALTER TABLE book_borrow_info AFEGEIX CONSTRAINT fk_borrowerCLAU ESTRANGERA(préstec_id)REFERÈNCIES prestataris(identificador)EN ESBORRAR CASCADA SOBRE RESTRICTE D'actualització;
Ara, inseriu un registre a book_borrow_info amb vàlid préstec_id valor que existeix a identificador camp de prestataris taula. 157.643 El valor existeix a la taula de prestataris i la sentència INSERT següent s'executarà amb èxit.
INSERTAR EN book_borrow_info VALORS('157.643', 1, '2020.03.10', '2020.03.20', 'Tornat');
La sentència INSERT següent generarà un missatge d'error perquè el valor de l'identificador 195.680 no existeix a la taula de prestataris.
INSERTAR EN book_borrow_info VALORS('195.680', 1, '2020.04.15', '2020.04.30', 'Tornat');
Conclusió:
Definir correctament les restriccions de claus externes és una tasca molt important per crear una base de dades relacional i gestionar les dades de manera adequada entre les taules. Conèixer els usos de restriccions de claus externes és molt essencial per als dissenyadors de bases de dades. Espero que aquest article ajudi els nous dissenyadors de bases de dades a entendre el concepte de restriccions de claus externes i aplicar-les adequadament en les seves tasques.