Avisar de contenido inadecuado

Clave primarias y foranes

{
}

3.6.6. Usar claves foráneas (foreign keys)

En MySQL, las tablas InnoDB soportan restricciones de claves foráneas. Consulte Capítulo 15, El motor de almacenamiento InnoDB. Consulte también Sección 1.7.5.5, “Claves foráneas (foreign keys)”.

No se requiere una restricción de clave foránea para simplemente unir dos tablas. Para otros tipos de tabla que no sean InnoDB, es posible, al momento de definir una columna, utilizar una cláusula REFERENCEStbl_name (col_name), la cual no tiene efecto real y funciona solamente como un recordatorio o comentario de que la columna que se está definiendo está dirigida a hacer referencia a una columna en otra tabla. Al emplear esta sintaxis es muy importante comprender que:

  • MySQL no efectúa ningún tipo de CHECK o comprobación para asegurarse de que col_name realmente existe en tbl_name (o incluso que tbl_name existe).
  • MySQL no realiza ningún tipo de acción sobre tbl_name tal como borrar filas en respuesta a acciones ejecutadas sobre filas en la tabla que se está definiendo; en otras palabras, esta sintaxis no produce por sí misma un comportamiento ON DELETE u ON UPDATE. (Inclusive cuando se puede escribir una cláusula ON DELETE u ON UPDATE como parte de la cláusula REFERENCES, estas son también ignoradas).
  • Esta sintaxis crea una columna; no crea ninguna clase de índice o campo clave.
  • Esta sintaxis causará un error si se la emplea durante la definición de una tabla InnoDB.

Una columna creada de esta forma se puede utilizar como columna de unión, como se muestra aquí:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
 
CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);
 
INSERT INTO person VALUES (NULL, 'Antonio Paz');
 
SELECT @last := LAST_INSERT_ID();
 
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
 
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
 
SELECT @last := LAST_INSERT_ID();
 
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
 
SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+
 
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+
 
 
SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';
 
+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

Cuando se usa de esta manera, la cláusula REFERENCES no es mostrada en la salida de SHOW CREATE TABLE o DESCRIBE:

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

El uso de REFERENCES como comentario o "recordatorio" en la definición de una columna funciona en tablas MyISAM y BerkeleyDB.

13.2.7.2. Sintaxis de UNION

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

UNION se usa para combinar el resultado de un número de comandos SELECT en un conjunto de resultados.

Las columnas seleccionadas lisatadas en posiciones correspondientes de cada comando SELECT deben tener el mismo tipo. (Por ejemplo, la primera columna seleccionada por el primer comando debe tener el mismo tipo que la primer columna seleccionada por otros comandos.) Los nombres de columna usados por el primer comando SELECT se usan como nombres de columna para los resultados retornados.

Los comandos SELECT son comandos select normales, pero con las siguientes restricciones:

  • Sólo el último comando SELECT puede usar INTO OUTFILE.
  • HIGH_PRIORITY no puede usarse con comandos SELECT que sean parte de una UNION. Si lo especifica para el primer SELECT, no tiene efecto. Si lo especifica para cualquier SELECT posterior, aparece un error de sintaxis.

Si no usa la palabra clave ALL para UNION, todos los registros retornados son únicos, como si hubiera hecho un DISTINCT para el conjunto de resultados total. Si especifica ALL, obtiene todos los registros coincidentes de todos los comandos SELECT usados.

La palabra clave DISTINCT es una palabra opcional que no tiene efecto, pero se permite en la sintaxis como requiere el estándar SQL . (En MySQL, DISTINCT representa el comportamiento por defecto de una union.)

En MySQL 5.0, puede mezclar UNION ALL y UNION DISTINCT en la misma consulta. Tipos de UNION mezclados se tratan de forma que una unión DISTINCT sobreescribe cualquier unión ALL a su izquierda. Una unión DISTINCT puede producirse explícitamente usando UNION DISTINCT o implícitamente usando UNION sin palabra clave DISTINCT o ALL a continuación.

Si quiere usar una cláusula ORDER BY o LIMIT para ordenar o limitar el resultado UNION entero, ponga entre paréntesis los comandos SELECT individuales y ponga el ORDER BY o LIMIT tras el último. El siguiente ejemplo usa ambas cláusulas:

(SELECT a FROM tbl_name WHERE a=10 AND B=1)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

Este tipo de ORDER BY no puede usar referencias de columnas que incluyan un nombre de columna (esto es, nombres en formato tbl_name.col_name ). En su lugar, proporcione un alias de columna al primer comando SELECT y refiérase al alias en el ORDER BY, o a la columna en el ORDER BY usando su posición de columna. (Un alias es preferible porque el uso de la posición de la columna está obsoleto.)

Para aplicar ORDER BY o LIMIT a un SELECT individual, ponga la cláusula dentro de los paréntesis alrededor del SELECT:

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Los ORDER BY para comandos SELECT individuales entre paréntesis tienen efecto sólo al combinarlos con LIMIT. De otro modo, el ORDER BY se optimiza a parte.

En MySQL 5.0, los tipos y longitudes de las columnas en el conjunto de resultados de una UNION tienen en cuenta los valores recibidos por todos los comandos SELECT. Por ejemplo, considere lo siguiente:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+

(En alguna versión anterior de MySQL, el segundo registro se habría truncado a una longitud de 1.)

3.6.7. Buscar usando dos claves

Un OR empleando una única clave es bien optimizado, como es el manejo de AND

El único caso difícil es la búsqueda sobre dos diferentes claves combinadas con OR:

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

Esto se ha optimizado a partir de MySQL 5.0.0. Consulte Sección 7.2.6, “Index Merge Optimization”.

En MySQL 5.0 tambien se puede resolver eficientemente este problema utilizando una UNION que combine la salida de dos sentencias SELECT separadas. Consulte Sección 13.2.7.2, “Sintaxis de UNION.

Cada sentencia SELECT busca en solamente una clave y puede ser optimizada:

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.

3.6.8. Calcular visitas diarias

El siguiente ejemplo muestra cómo se pueden utilizar las funciones de bits para calcular la cantidad de dias de un mes que un usuario ha visitado una página Web.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

La tabla del ejemplo contiene valores de año, mes y dia que representan las visitas de los usuarios a la página. Para determinar en cuántos días diferentes del mes se produjeron las visitas, se emplea esta consulta:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

La cual devuelve:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

La consulta calcula cuantos días diferentes aparecen en la tabla para cada combinación de año y mes, removiendo automáticamente las entradas duplicadas.

3.6.9. Utilización de AUTO_INCREMENT

El atributo AUTO_INCREMENT puede utilizarse para generar un identificador único para cada nueva fila:

CREATE TABLE animals (
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (id)
             );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),
                                  ('lax'),('whale'),('ostrich');
SELECT * FROM animals;

Lo cual devuelve:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Para obtener el valor AUTO_INCREMENT más recientemente generado se puede utilizar la funcion SQL LAST_INSERT_ID() o la función del API de C mysql_insert_id(). Estas funciones son específicas de cada conexión, de modo que su valor de retorno no es afectado por las inserciones realizadas a través de otras conexiones.

Nota: Para una inserción de múltiples filas, LAST_INSERT_ID()/mysql_insert_id() retornan el valor AUTO_INCREMENT de la primera de las filas insertadas. Esto permite que las inserciones de múltiples filas sean reproducidas correctamente en otros servidores en una configuración de replicación.

Para tablas MyISAM y BDB se puede especificar AUTO_INCREMENT sobre una columna secundaria en un índice de múltiples columnas. En este caso, el valor generado para la columna AUTO_INCREMENT es calculado como MAX(auto_increment_column)+1 WHERE prefix=given-prefix. Esto es útil cuando se desea colocar datos en grupos ordenados.

CREATE TABLE animals (
             grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
             );
INSERT INTO animals (grp,name) VALUES('mammal','dog'),('mammal','cat'),
                  ('bird','penguin'),('fish','lax'),('mammal','whale'),
                  ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

Lo cual devuelve:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Nótese que en este caso (cuando la columna AUTO_INCREMENT es parte de un índice de múltiples columnas), los valores AUTO_INCREMENT son reutilizados si se elimina la fila con el valor AUTO_INCREMENT más alto en cualquier grupo. Esto ocurre incluso para tablas MyISAM, en las que los valores AUTO_INCREMENT normalmente no son reutilizados

Si la columna AUTO_INCREMENT es parte de varios índices, MySQL generará valores secuenciales empleando el índice que comienza con la columna AUTO_INCREMENT, si hay uno. Por ejemplo, si la tabla animals contiene los índices PRIMARY KEY (grp, id) e INDEX (id), MySQL ignoraría el índice PRIMARY KEY al generar valores secuenciales. Como resultado, la tabla contendría una secuencia simple, sin considerar el valor grp.

 

{
}
{
}

Deja tu comentario Clave primarias y foranes

Identifícate en OboLog, o crea tu blog gratis si aún no estás registrado.

Avatar Tu nombre