Restricciones de clave externa
Reimpreso de: /duck_arrow/article/details/8264686
Concepto:
La clave principal (clave principal) puede identificar de forma única un atributo o grupo de atributos de una fila en una tabla. Una tabla sólo puede tener una clave principal, pero puede tener varios índices candidatos. Las claves primarias a menudo forman restricciones de integridad referencial con claves externas para evitar inconsistencias en los datos. La clave principal puede garantizar que el registro sea único y que el campo de clave principal no esté vacío. El sistema de administración de bases de datos genera automáticamente un índice único para la clave principal, por lo que la clave principal también es un índice especial.
Una clave externa es una o más columnas que se utilizan para establecer y fortalecer el vínculo entre los datos de dos tablas. Las restricciones de clave externa se utilizan principalmente para mantener la coherencia de los datos entre dos tablas. En resumen, la clave externa de una tabla es la clave principal de otra tabla y la clave externa conecta las dos tablas. En circunstancias normales, para eliminar la clave principal de una tabla, primero debe asegurarse de que no haya claves externas idénticas en otras tablas (es decir, la clave principal de la tabla no tiene una clave externa asociada).
El índice se utiliza para encontrar rápidamente registros con valores específicos. Es principalmente para facilitar la recuperación y acelerar el acceso. Se crea de acuerdo con ciertas reglas y generalmente desempeña una función de clasificación. El llamado índice único es básicamente el mismo que el "índice ordinario" anterior, pero hay una diferencia: todos los valores en la columna de índice solo pueden aparecer una vez, es decir, deben ser únicos.
Resumen:
La clave principal debe ser un índice único, pero un índice único no es necesariamente la clave principal.
Una tabla puede tener múltiples índices únicos, pero solo puede haber una clave principal.
Las columnas de clave principal no permiten valores nulos, mientras que las columnas de índice único permiten valores nulos.
Otros campos pueden hacer referencia a la clave principal como clave externa, pero no se puede hacer referencia al índice como clave externa.
Clave principal:
La clave principal es el único índice de la tabla de datos. Por ejemplo, la tabla de estudiantes tiene ID y nombre de estudiante. Los nombres pueden tener el mismo nombre. la identificación del estudiante es realmente única. Si desea buscar un registro de la tabla de estudiantes, como buscar una persona, solo puede buscar según el número de estudiante. Solo entonces podrá encontrar el único, que es el. clave primaria; como: idint(10) clave primaria no nula auto_increment; tipo de aumento automático;
Clave externa:
Definir tabla de datos
Supongamos que El fabricante de computadoras tiene información de productos sobre máquinas completas y accesorios en su base de datos. La tabla utilizada para guardar la información del producto de la máquina completa se llama Pc; la tabla utilizada para guardar la información de suministro de accesorios se llama Piezas.
Hay un campo en la tabla de PC que describe el modelo de CPU utilizado en esta computadora;
Hay un campo correspondiente en la tabla de Piezas que describe el modelo de CPU, podemos. Piense en ello como una lista de todos los modelos de CPU.
Evidentemente la CPU utilizada por el ordenador producido por este fabricante debe ser el modelo que existe en la tabla de información de suministro (piezas). En este momento, existe una relación de restricción entre las dos tablas: el modelo de CPU en la tabla Pc está restringido por el modelo en la tabla Piezas.
Primero creamos la tabla de piezas:
CREATE TABLE parts (
... Definición de campo...,
modelo VARCHAR (20) NOT NULL,
... Definición de campo...
A continuación está la tabla Pc:
CREAR TABLA pc (
... definición de campo...,
cpumodel VARCHAR(20) NOT NULL,
... definición de campo...
};
Establecer índice
Para establecer una clave externa, en la tabla de referencia (tabla de referencia, es decir, tabla Pc) y en la tabla de referencia (es decir, tabla de referencia , es decir, tabla de piezas), los dos campos correspondientes deben estar indexados
Para la tabla de piezas:
ALTER TABLE parts ADD INDEX idx_model (modelo). Esta oración significa agregar un índice a la tabla de piezas. El índice se construye en el campo del modelo. Asigne a este índice un nombre idx_model. Es similar a la tabla Pc:
. ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);
De hecho, estos dos índices se pueden configurar al crear la tabla. Esto es solo para resaltar su necesidad
Definir claves externas<. /p>
La siguiente es la "restricción" mencionada anteriormente entre las dos tablas. Debido a que el modelo de CPU de la PC debe hacer referencia al modelo correspondiente en la tabla de piezas, cambiamos el campo modelo de CPU de la tabla de PC. a "clave externa" (FOREIGNKEY), es decir, el valor de referencia de esta clave proviene de otras tablas
ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCIAS parts(model);
La primera línea es para configurar una clave externa para la tabla de PC y darle un nombre a esta clave externa fk_cpu_model; la segunda línea es para configurar el modelo de cpu; esta tabla El campo está configurado como clave externa; la tercera línea dice que las restricciones de esta clave externa provienen del campo modelo de la tabla Partes
De esta manera, nuestra clave externa funcionará si lo hacemos. intente CREAR una PC, el modelo de CPU que utiliza no existe en la tabla de Partes, entonces MySQL prohibirá que se CREE esta PC.
Operación en cascada
Considere la siguiente situación:
El técnico descubrió que en una determinada serie de CPU (posiblemente hay muchos modelos) se había ingresado una letra incorrecta. el número de modelo y ahora es necesario corregirlo. Lo que esperamos es que cuando cambie la columna de referencia en la tabla de piezas, la columna de referencia en la tabla correspondiente también se pueda corregir automáticamente.
Al definir la clave externa, puede agregar la siguiente palabra clave al final:
ON UPDATE CASCADE; es decir, cuando se actualiza la tabla principal, la(s) tabla(s) secundaria(s) Generará una acción de actualización en cadena. Parece que a algunas personas les gusta llamar a esta operación "en cascada".
:)
Si escribe esta declaración completa, es:
ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
CLAVE EXTRANJERA (cpumodel)
REFERENCIAS piezas(modelo)
ON UPDATE CASCADE;
Además de CASCADE, también hay RESTRICT (deshabilita los cambios en la tabla principal), SET NULL (los campos correspondientes del la subtabla está configurada como vacía) Esperar operaciones
Índice:
El índice se utiliza para encontrar rápidamente registros con valores específicos. Todos los índices de MySQL se guardan en forma de árboles B. . Si no hay índice, al ejecutar una consulta, MySQL debe escanear todos los registros en toda la tabla comenzando desde el primer registro hasta encontrar un registro que cumpla con los requisitos. Cuanto mayor sea el número de registros en la tabla, mayor será el costo de esta operación. Si se ha creado un índice en la columna utilizada como condición de búsqueda, MySQL puede obtener rápidamente la ubicación del registro de destino sin escanear ningún registro. Si la tabla tiene 1000 registros, buscar registros a través del índice es al menos 100 veces más rápido que escanear los registros secuencialmente.
Supongamos que creamos una tabla llamada personas:
CREATE TABLE people (peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL);
Luego, inserte 1000 valores de nombres diferentes en la tabla de personas de forma completamente aleatoria. La siguiente figura muestra una pequeña parte del archivo de datos donde se encuentra la tabla de personas:
Como puede ver, la columna de nombre no tiene ningún orden claro en el archivo de datos. Si creamos un índice en la columna de nombre, MySQL ordenará la columna de nombre en el índice:
Para cada elemento en el índice, MySQL guarda internamente un "puntero" a la ubicación real del registro en el archivo de datos. . Entonces, si queremos encontrar el ID de persona del registro cuyo nombre es igual a "Mike" (el comando SQL es "SELECTpeopleid FROM people WHEREname='Mike';"), MySQL puede encontrar el valor de "Mike" en el índice de nombre y luego vaya directamente al archivo de datos. La fila correspondiente en la fila devuelve exactamente el ID de persona (999) de esa fila. Durante este proceso, MySQL solo necesita procesar una fila para devolver el resultado. Si no hay un índice en la columna "nombre", MySQL escaneará todos los registros en el archivo de datos, es decir, ¡1000 registros! Obviamente, cuanto menor sea la cantidad de registros que MySQL necesita procesar, más rápido podrá completar la tarea. Tipos de índices
MySQL proporciona una variedad de tipos de índices para elegir:
Índice ordinario
Este es el tipo de índice más básico y no tiene unicidad. o similares. Los índices ordinarios se pueden crear de las siguientes maneras:
Crear un índice, como CREATE INDEX lt; el nombre del índice gt; ON tablename (lista de columnas, como ALTER TABLE); nombre de tabla AGREGAR ÍNDICE [índice de nombre] (lista de columnas Especifique el índice al crear la tabla, como CREATE TABLE nombre de tabla ([...], ÍNDICE [nombre de índice] (lista de columnas));
Índice único
Este tipo de índice es básicamente el mismo que el "índice ordinario" anterior, pero hay una diferencia: todos los valores en la columna de índice solo pueden aparecer una vez, es decir , deben ser únicos.
Se puede crear un índice único de las siguientes maneras:
Crear un índice, como CREATE UNIQUE INDEX lt; el nombre del índice gt; ON tablename (lista de columnas, como por ejemplo); ALTER TABLE nombre de tabla ADD UNIQUE [nombre del índice] (lista de columnas Especifique el índice al crear la tabla, como CREATE TABLE nombre de tabla ([...], UNIQUE [nombre del índice] (lista de columnas));
Clave principal
La clave principal es un índice único, pero debe especificarse como "PRIMARYKEY". Si alguna vez ha trabajado con columnas de tipo AUTO_INCREMENT, es posible que ya esté familiarizado con conceptos como claves primarias. La clave principal generalmente se especifica al crear la tabla, como "CREATETABLE nombre de tabla ([...], PRIMARYKEY (lista de columnas));". Sin embargo, también podemos agregar claves primarias modificando la tabla, como "ALTER TABLE nombre de tabla ADD PRIMARY KEY (lista de columnas);". Cada tabla sólo puede tener una clave principal.
Índice de texto completo
MySQL admite la indexación de texto completo y la búsqueda de texto completo a partir de la versión 3.23.23. En MySQL, el tipo de índice de texto completo es FULLTEXT. Los índices de texto completo se pueden crear en columnas de tipo VARCHAR o TEXT. Puede crearse mediante el comando CREATETABLE, o mediante el comando ALTER TABLE o CREATE INDEX. Para conjuntos de datos grandes, crear un índice de texto completo mediante el comando ALTERTABLE (o CREATEINDEX) es más rápido que insertar registros en una tabla vacía con un índice de texto completo. La discusión a continuación en este artículo ya no cubre índices de texto completo. Para obtener más información, consulte la documentación de MySQL.
Índice de una sola columna e índice de varias columnas
El índice puede ser un índice de una sola columna o un índice de varias columnas. A continuación utilizamos ejemplos específicos para ilustrar las diferencias entre estos dos índices. Supongamos que existe una tabla de personas:
CREATE TABLE people (peopleid SMALLINT NOT NULL AUTO_INCREMENT, nombre CHAR(50) NOT NULL, apellido CHAR(50) NOT NULL, edad SMALLINT NOT NULL, townid SMALLINT NOT NULL , PRIMARY KEY (peopleid) );
Los siguientes son los datos que insertamos en la tabla de personas:
Hay cuatro personas llamadas "Mikes" en este fragmento de datos (dos de ellas Había dos jóvenes de 17 años (de apellido Sullivan, dos McConnell) y uno con el inusual nombre de Joe Smith.
El objetivo principal de esta tabla es devolver el ID de persona correspondiente según el apellido, el nombre y la edad del usuario especificado. Por ejemplo, es posible que necesitemos encontrar el ID de persona de un usuario cuyo nombre es MikeSullivan y cuya edad es 17 años (el comando SQL es SELECT peopleid FROM people WHEREfirstname='Mike' AND lastname='Sullivan' ANDage=17;). Como no queremos que MySQL escanee toda la tabla cada vez que se ejecuta una consulta, aquí se deben considerar los índices.
Primero, podemos considerar la creación de un índice en una sola columna, como la columna de nombre, apellido o edad.
Si creamos un índice en la columna de nombre (ALTERTABLE people ADD INDEX firstname(firstname);), MySQL limitará rápidamente el alcance de la búsqueda a aquellos registros con firstname='Mike' a través de este índice, y luego procederá con este "conjunto de resultados intermedio". "Buscar otras condiciones: Primero excluye aquellos registros cuyo apellido no sea igual a "Sullivan", y luego excluye aquellos registros cuya edad no sea igual a 17. Cuando el registro cumple con todas las condiciones de búsqueda, MySQL devuelve los resultados finales de la búsqueda.
Debido al establecimiento de un índice en la columna del nombre, MySQL es mucho más eficiente que realizar un escaneo completo de la tabla, pero la cantidad de registros que requerimos que MySQL escanee sigue siendo mucho mayor de lo que es. realmente necesario. Aunque podemos eliminar el índice en la columna de nombre y crear un índice en la columna de apellido o edad, en general, la eficiencia de la búsqueda sigue siendo similar sin importar en qué columna se cree el índice.
Para mejorar la eficiencia de la búsqueda, debemos considerar el uso de índices de varias columnas. Si crea un índice de varias columnas para las tres columnas nombre, apellido y edad, MySQL puede encontrar los resultados correctos en una sola búsqueda. El siguiente es el comando SQL para crear este índice de varias columnas:
ALTER TABLE people ADD INDEX fname_lname_age (nombre, apellido, edad);
Dado que el archivo de índice se guarda en B -Formato de árbol, MySQL puede ir inmediatamente al nombre apropiado, luego al apellido apropiado y finalmente a la edad apropiada. ¡MySQL encontró correctamente el registro de destino de la búsqueda sin escanear ningún registro en el archivo de datos!
Entonces, si crea un índice de una sola columna en las columnas de nombre, apellido y edad, ¿el efecto será el mismo que crear un índice de varias columnas en nombre, apellido y edad? La respuesta es no, son completamente diferentes. Cuando ejecutamos una consulta, MySQL solo puede usar un índice. Si tiene tres índices de una sola columna, MySQL intentará elegir el índice más restrictivo. Sin embargo, incluso el índice de una sola columna más restrictivo es ciertamente mucho menos restrictivo que el índice de varias columnas en las columnas de nombre, apellido y edad.
Prefijo más a la izquierda
Los índices de varias columnas tienen otra ventaja, que se refleja en un concepto llamado Prefijo más a la izquierda. Continuando con el ejemplo anterior, ahora tenemos un índice de varias columnas en las columnas de nombre, apellido y edad. Llamamos a este índice fname_lname_age. Cuando la condición de búsqueda es una combinación de las siguientes columnas, MySQL utilizará el índice fname_lname_age:
nombre, apellido, edad nombre, apellido nombre
Desde otra perspectiva, es equivalente a Creamos índices en la combinación de columnas (nombre, apellido, edad), (nombre, apellido) y (nombre).
Las siguientes consultas pueden utilizar este índice fname_lname_age:
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17' SELECCIONE peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; Las siguientes consultas no pueden utilizar el índice en absoluto: SELECT peopleid FROM people WHERE lastname='Sullivan'; peopleid FROM people WHERE lastname='Sullivan' AND age='17';
Seleccionar columnas de índice
En el proceso de optimización del rendimiento, elegir en qué columnas crear índices es lo más importante uno de los pasos. Hay dos tipos principales de columnas que se pueden considerar para usar índices: columnas que aparecen en la cláusula WHERE y columnas que aparecen en la cláusula de unión. Mire la siguiente consulta:
SELECT edad ## No use el índice FROM people WHERE firstname='Mike' ## Considere usar el índice AND lastname='Sullivan' ## Considere usar el índice
Esta consulta es ligeramente diferente a la consulta anterior, pero sigue siendo una consulta simple. Dado que se hace referencia a la edad en la parte SELECT, MySQL no la usará para restringir las operaciones de selección de columnas. Por lo tanto, para esta consulta, no es necesario crear un índice en la columna de edad. Aquí hay un ejemplo más complejo:
SELECT people.age, ##No use el índice town.name ##No use el índice FROM people LEFT JOIN town ON people.townid=town.townid # #Considere usar el índice WHERE firstname='Mike' ##Considere usar el índice AND lastname='Sullivan' ##Considere usar el índice
Igual que el ejemplo anterior, ya que el nombre y el apellido aparecen en la cláusula WHERE , estos dos Aún es necesario crear un índice para cada columna. Además, dado que la columna townid de la tabla de ciudades aparece en la cláusula de unión, debemos considerar la posibilidad de crear un índice en esta columna.
Índice único:
El índice único permite que la columna de datos indexados contenga valores duplicados. Por ejemplo, debido a que las personas pueden tener el mismo nombre, el mismo nombre puede aparecer dos o más veces en la misma tabla de datos del "Perfil del empleado".
Si puede estar seguro de que una determinada columna de datos solo contendrá valores diferentes entre sí, debe utilizar la palabra clave UNIQUE para definirla como un índice único al crear un índice para estos datos. columna. Los beneficios de esto son: primero, simplifica la administración de este índice por parte de MySQL y el índice se vuelve más eficiente; segundo, MySQL verificará automáticamente el valor de este campo en el nuevo registro cuando se inserte un nuevo registro en la tabla de datos. este campo ya apareció en un registro; de ser así, MySQL se negará a insertar el nuevo registro. En otras palabras, un índice único puede garantizar la unicidad de los registros de datos.
De hecho, en muchas ocasiones, el propósito de crear índices únicos no es mejorar la velocidad de acceso, sino simplemente evitar la duplicación de datos.
Varios conceptos de MySQL: clave primaria, clave externa, índice, índice único
Etiqueta: ¿Qué padpeople han completado el bletext de tecnología con cambio de nombre?