La sintaxis de la instrucción alter table. Manual de referencia de MySQL ALTER TABLE para agregar y eliminar columnas, valores predeterminados

ALTER TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:

AÑADIR create_definition

o AÑADIR (create_definition, create_definition, ...)

o AGREGAR ÍNDICE (index_col_name, ...)

o AÑADIR CLAVE PRIMARIA (index_col_name, ...)

o AÑADIR ÚNICO (index_col_name, ...)

o AÑADIR FULLTEXT (index_col_name, ...)

o AÑADIR CLAVE EXTRANJERA index_name (index_col_name, ...)

o ALTER col_name (SET DEFAULT literal | DROP DEFAULT)

o CAMBIAR old_col_name create_definition

o MODIFICAR create_definition

o DROP col_name

o DEJAR LLAVE PRIMARIA

o DROP INDEX index_name

o DESACTIVAR TECLAS

o HABILITAR LLAVES

o RENAME new_tbl_name

o PEDIDO POR col

o table_options

La instrucción ALTER TABLE proporciona la capacidad de modificar la estructura de una tabla existente. Por ejemplo, puede agregar o eliminar columnas, crear o eliminar índices o cambiar el nombre de las columnas o de la tabla en sí. También puede cambiar el comentario de la tabla y su tipo.

La instrucción ALTER TABLE crea una copia temporal de la tabla original en tiempo de ejecución. El cambio solicitado se realiza en la copia, luego se elimina la tabla original y se cambia el nombre de la nueva. Esto se hace para que todas las actualizaciones, excepto las fallidas, se incluyan automáticamente en la nueva tabla. En tiempo de ejecución ALTER TABLE, otros clientes pueden leer la tabla de origen. Las actualizaciones y escrituras en esta tabla se pausan hasta que la nueva tabla esté lista.

Cabe señalar que cuando se usa cualquier otra opción para ALTER TABLE que no sea RENAME, MySQL siempre creará una tabla temporal, incluso si los datos, estrictamente hablando, no necesitan ser copiados (por ejemplo, si el nombre de una columna es cambió). Para las tablas MyISAM, es posible aumentar la velocidad de reconstrucción de la parte del índice (que es la parte más lenta en el proceso de reconstrucción de la tabla) estableciendo la variable myisam_sort_buffer_size lo suficientemente grande.

Para usar la instrucción ALTER TABLE, necesita los privilegios ALTER, INSERT y CREATE en la tabla.

IGNORE es una extensión de MySQL para ANSI SQL92. Controla cómo funciona ALTER TABLE cuando hay claves únicas duplicadas en la nueva tabla. Si no se especifica la opción IGNORE, el proceso se cancela y se revierte para esta copia. Si se especifica Ignorar, para las filas con claves únicas duplicadas, solo se utiliza la primera fila y el resto se elimina.

Puede ejecutar varias sentencias ADD, ALTER, DROP y CHANGE en un solo comando ALTER TABLE. Esta es una extensión de MySQL para ANSI SQL92, donde solo se permite una de las expresiones mencionadas en un solo comando ALTER TABLE.

Las opciones CHANGE col_name, DROP col_name y DROP INDEX también son extensiones de MySQL para ANSI SQL92.

La opción MODIFY es una extensión de Oracle para el comando ALTER TABLE.

La palabra opcional COLUMNA representa "ruido blanco" y se puede omitir.

Cuando se usa ALTER TABLE table_name RENAME TO new_name sin ninguna otra opción, MySQL simplemente cambia el nombre de los archivos que coinciden con la tabla dada. En este caso, no es necesario crear una tabla temporal. La expresión create_definition usa la misma sintaxis para ADD y CHANGE que para CREATE TABLE. Tenga en cuenta que esta sintaxis incluye el nombre de la columna, no solo su tipo.

Se puede cambiar el nombre de una columna usando la expresión CHANGE column_name create_definition. Para hacer esto, debe especificar los nombres de columna nuevos y antiguos y el tipo actual de columna. Por ejemplo, para cambiar el nombre de la columna INTEGER a b, puede hacer lo siguiente:

mysql> ALTER TABLE t1 CAMBIAR a b INTEGER;

Si cambia el tipo de una columna, pero no su nombre, la sintaxis CHANGE aún requiere ambos nombres de columna, incluso si son iguales. Por ejemplo:

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

Sin embargo, a partir de MySQL 3.22.16a, también puede usar la expresión MODIFY para cambiar el tipo de una columna sin cambiarle el nombre:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

Cuando usa CHANGE o MODIFY para reducir la longitud de la columna en la que se construye el índice (por ejemplo, un índice en los primeros 10 caracteres de una columna VARCHAR), no puede hacer que la columna sea más corta que el número de caracteres indexados.

Al cambiar el tipo de una columna usando CHANGE o MODIFY, MySQL intenta convertir los datos al nuevo tipo lo más correctamente posible.

En MySQL 3.22 y posteriores, puede usar FIRST o ADD ... DESPUÉS de column_name para agregar una columna en una posición determinada dentro de una fila de la tabla. De forma predeterminada, la columna se agrega al final. A partir de MySQL 4.0.1, también puede usar las palabras clave FIRST y DESPUÉS en las opciones CAMBIAR o MODIFICAR.

La opción ALTER COLUMN establece la columna en un nuevo valor predeterminado o elimina el anterior. Si se elimina el valor predeterminado anterior y la columna dada puede ser NULL, el nuevo valor predeterminado será NULL. Si la columna no puede ser NULL, MySQL asigna un valor predeterminado. La opción DROP INDEX elimina el índice. Esta es una extensión de MySQL sobre ANSI SQL92. Si las columnas se eliminan de una tabla, esas columnas también se eliminan de cualquier índice del que formen parte. Si se eliminan todas las columnas que componen un índice, ese índice también se elimina.

Si la tabla contiene solo una columna, esa columna no se puede eliminar. En su lugar, puede eliminar la tabla dada usando el comando DROP TABLE.

La opción DROP PRIMARY KEY elimina el índice principal. Si dicho índice no existe en la tabla dada, entonces se descarta el primer índice ÚNICO en esta tabla. (MySQL marca la primera clave única UNIQUE como PRIMARY KEY si no se ha especificado explícitamente ninguna otra PRIMARY KEY). Cuando agrega UNIQUE INDEX o PRIMARY KEY a una tabla, se almacenan antes que otras claves no únicas para que las claves duplicadas se puedan identificar lo antes posible.

ORDER BY le permite crear una nueva tabla con filas en el orden especificado. Tenga en cuenta que la tabla creada no conservará este orden de filas después de las operaciones de inserción y eliminación. En algunos casos, esta característica puede facilitar la operación de clasificación en MySQL si la tabla tiene un diseño de columna que le gustaría tener en el futuro. Esta opción es principalmente útil si conoce de antemano un cierto orden en el que se consultarán principalmente las filas. El uso de esta opción después de transformaciones importantes de la tabla brinda la oportunidad de obtener un mejor rendimiento.

Cuando se usa el comando ALTER TABLE en tablas MyISAM, todos los índices no únicos se crean en un paquete separado (como REPAIR). Esto hace que el comando ALTER TABLE sea más rápido cuando hay varios índices.

A partir de MySQL 4.0, la función anterior se puede habilitar explícitamente. El comando ALTER TABLE ... DISABLE KEYS evita que MySQL actualice índices no únicos en tablas MyISAM. A continuación, puede utilizar el comando ALTER TABLE ... ENABLE KEYS para volver a crear los índices que faltan. Dado que MySQL hace esto usando un algoritmo especial que es mucho más rápido que insertar claves una por una, bloquear las claves puede ser una aceleración significativa en matrices de inserción grandes.

Usando la función mysql_info () de la API de C, puede determinar cuántos registros se copiaron y también (usando IGNORE) cuántos registros se eliminaron debido a valores de clave únicos duplicados.

Las instrucciones FOREIGN KEY, CHECK y REFERENCES en realidad no hacen nada. Se introdujeron solo por razones de compatibilidad, para facilitar la migración del código de otros servidores SQL y la ejecución de aplicaciones que crean tablas de referencia.

A continuación se muestran ejemplos que muestran algunos de los usos del comando ALTER TABLE. El ejemplo comienza con la tabla t1, que se crea de la siguiente manera:

mysql> CREAR TABLA t1 (a INTEGER, b CHAR (10));

Para cambiar el nombre de una tabla de t1 a t2:

mysql> ALTER TABLE t1 RENAME t2;

Para cambiar el tipo de una columna de INTEGER a TINYINT NOT NULL (manteniendo el mismo nombre) y cambiar el tipo de la columna bcCHAR (10) a CHAR (20) y renombrarlo cbnc:

mysql> ALTER TABLE t2 MODIFICAR a TINYINT NO NULO, CAMBIAR b c CHAR (20);

Para agregar una nueva columna TIMESTAMP llamada d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Para agregar un índice a la columna dy convertir la columna a en la clave principal:

mysql> ALTER TABLE t2 AÑADIR ÍNDICE (d), AÑADIR CLAVE PRIMARIA (a);

Para eliminar la columna c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Para agregar una nueva columna numérica AUTO_INCREMENT llamada c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,

Tenga en cuenta que la columna está indexada porque las columnas AUTO_INCREMENT deben estar indexadas; además, la columna c se declara NOT NULL porque las columnas indexadas no pueden ser NULL.

Cuando agrega una columna AUTO_INCREMENT, los valores en esa columna se completan automáticamente con números secuenciales (a medida que se agregan registros). El primer número de secuencia se puede configurar ejecutando el comando SET INSERT_ID = # con ALTER TABLE o usando la opción de tabla AUTO_INCREMENT = #.

ALTER TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD create_definition o ADD (create_definition, create_definition, ...) o ADD INDEX (index_col_name, ...) o ADD PRIMARY KEY (index_col_name, ...) o ADD UNIQUE (index_col_name, ...) o ADD FULLTEXT (index_col_name, ...) o ADD FOREIGN KEY index_name (index_col_name, ...) o ALTER col_name (SET DEFAULT literal | DROP DEFAULT) o CAMBIAR old_col_name create_definition o MODIFY create_definitionname o DRO o DROP PRIMARY KEY o DROP INDEX index_name o DISABLE KEYS o ENABLE KEYS o RENAME new_tbl_name u ORDER BY col o table_options

Si la instrucción ALTER TABLE se usa para modificar la definición de tipo de una columna, pero DESCRIBE tbl_name indica que la columna no ha cambiado, entonces MySQL puede estar ignorando la modificación por una de las razones descritas en la sección 6.5.3.1 Cambios silenciosos en la definición de columnas. Por ejemplo, al intentar cambiar una columna VARCHAR a CHAR, MySQL continuará usando VARCHAR si la tabla dada contiene otras columnas de longitud variable.

La instrucción ALTER TABLE, en tiempo de ejecución, crea una copia temporal de la tabla original. El cambio solicitado se realiza en la copia, luego se elimina la tabla original y se cambia el nombre de la nueva. Esto se hace para que todas las actualizaciones, excepto las fallidas, se incluyan automáticamente en la nueva tabla. Durante la ejecución de ALTER TABLE, otros clientes pueden leer la tabla original. Las actualizaciones y escrituras en esta tabla se pausan hasta que la nueva tabla esté lista.

Cabe señalar que cuando se usa cualquier otra opción para ALTER TABLE que no sea RENAME, MySQL siempre creará una tabla temporal, incluso si los datos, estrictamente hablando, no necesitan ser copiados (por ejemplo, cuando el nombre de una columna es cambió). Planeamos arreglar esto en el futuro, sin embargo, dado que ALTER TABLE no se ejecuta con mucha frecuencia, nosotros (los desarrolladores de MySQL) no consideramos esto como una prioridad. Para las tablas MyISAM, puede aumentar la velocidad de reconstrucción de la parte del índice (que es la parte más lenta del proceso de reconstrucción de la tabla) configurando la variable myisam_sort_buffer_size lo suficientemente alta.

Para utilizar la instrucción ALTER TABLE, necesita los privilegios ALTER, INSERT y CREATE en la tabla.
IGNORE es una extensión de MySQL sobre ANSI SQL92. Controla cómo funciona ALTER TABLE cuando hay claves únicas duplicadas en la nueva tabla. Si no se especifica IGNORE, el proceso se interrumpe para esta copia y se revierte. Si se especifica IGNORE, para las filas con claves únicas duplicadas, solo se usa la primera fila y el resto se eliminan.
Puede ejecutar varias sentencias ADD, ALTER, DROP y CHANGE en un solo comando ALTER TABLE. Esta es una extensión de MySQL para ANSI SQL92, donde solo se permite una de las expresiones mencionadas en un comando ALTER TABLE.
Las opciones CHANGE col_name, DROP col_name y DROP INDEX también son extensiones de MySQL para ANSI SQL92.
La opción MODIFY es una extensión de Oracle para el comando ALTER TABLE.
La palabra opcional COLUMNA representa ruido blanco y se puede omitir.
Cuando se usa ALTER TABLE table_name RENAME TO new_name sin ninguna otra opción, MySQL simplemente cambia el nombre de los archivos que coinciden con la tabla dada. En este caso, no es necesario crear una tabla temporal. Consulte la sección 6.5.5 Sintaxis de RENAME TABL E.
La instrucción create_definition para ADD y CHANGE usa la misma sintaxis que para CREATE TABLE. Tenga en cuenta que esta sintaxis incluye el nombre de la columna, no solo su tipo. Consulte la sección 6.5.3 Sintaxis de CREATE TABLE.
Se puede cambiar el nombre de una columna usando la expresión CHANGE column_name create_definition. Para hacer esto, debe especificar los nombres de columna nuevos y antiguos y el tipo actual de columna. Por ejemplo, para cambiar el nombre de una columna INTEGER de a a b, puede hacer lo siguiente: mysql> ALTER TABLE t1 CHANGE a b INTEGER; Si cambia el tipo de una columna, pero no su nombre, la sintaxis CHANGE aún requiere que se especifiquen ambos nombres de columna, incluso si son iguales. Por ejemplo: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; Sin embargo, comenzando con MySQL 3.22.16a, también puede usar la expresión MODIFY para cambiar el tipo de una columna sin renombrarla: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
Cuando utilice CHANGE o MODIFY para reducir la longitud de la columna en la que se construye el índice (por ejemplo, un índice en los primeros 10 caracteres de una columna VARCHAR), no puede hacer que la columna sea más corta que el número de caracteres indexados.
Al cambiar el tipo de una columna usando CHANGE o MODIFY, MySQL intenta convertir los datos al nuevo tipo lo más correctamente posible.
En MySQL 3.22 y posteriores, puede usar FIRST o ADD ... DESPUÉS de column_name para agregar una columna en una posición determinada dentro de una fila de la tabla. De forma predeterminada, la columna se agrega al final. A partir de MySQL 4.0.1, también puede usar las palabras clave FIRST y DESPUÉS en las opciones CAMBIAR o MODIFICAR.
La opción ALTER COLUMN establece la columna en un nuevo valor predeterminado o elimina el anterior. Si se elimina el valor predeterminado anterior y la columna dada puede ser NULL, el nuevo valor predeterminado será NULL. Si la columna no puede ser NULL, entonces MySQL asigna un valor predeterminado como se describe en la sección 6.5.3 Sintaxis de CREATE TABLE.
La opción DROP INDEX elimina el índice. Esta es una extensión de MySQL sobre ANSI SQL92. Consulte la sección 6.5.8 Sintaxis de DROP INDEX.
Si las columnas se eliminan de una tabla, esas columnas también se eliminan de cualquier índice del que formen parte. Si se eliminan todas las columnas que componen un índice, ese índice también se elimina.
Si la tabla contiene solo una columna, esa columna no se puede eliminar. En su lugar, puede eliminar la tabla dada usando el comando DROP TABLE.
La opción DROP PRIMARY KEY elimina el índice principal. Si no existe tal índice en la tabla dada, entonces se descarta el primer índice ÚNICO en esa tabla. (MySQL marca la primera clave ÚNICA única como PRIMARY KEY si no se ha especificado explícitamente ninguna otra PRIMARY KEY). Cuando agrega UNIQUE INDEX o PRIMARY KEY a una tabla, se almacenan antes que otras claves no únicas para que las claves duplicadas se puedan identificar lo antes posible.
La opción ORDER BY le permite crear una nueva tabla con las filas en el orden especificado. Tenga en cuenta que la tabla creada no conservará este orden de filas después de las operaciones de inserción y eliminación. En algunos casos, esto puede facilitar la clasificación en MySQL si la tabla tiene un diseño de columna que le gustaría tener en el futuro. Esta opción es principalmente útil si conoce de antemano un cierto orden en el que se consultarán principalmente las filas. El uso de esta opción después de transformaciones importantes de la tabla brinda la oportunidad de obtener un mejor rendimiento.
Cuando se usa el comando ALTER TABLE en tablas MyISAM, todos los índices no únicos se crean en un paquete separado (como REPAIR). Esto hace que el comando ALTER TABLE sea más rápido cuando hay varios índices.
A partir de MySQL 4.0, la función anterior se puede habilitar explícitamente. ALTER TABLE ... DISABLE KEYS bloquea MySQL para que no actualice índices no únicos en tablas MyISAM. Luego puede usar el comando ALTER TABLE ... ENABLE KEYS para recrear los índices que faltan. Dado que MySQL hace esto usando un algoritmo especial que es mucho más rápido que insertar claves una por una, bloquear las claves puede ser una aceleración significativa en matrices de inserción grandes.
Con la función mysql_info () de la API de C, puede determinar cuántos registros se copiaron y también (al usar IGNORE) cuántos registros se eliminaron debido a valores de clave únicos duplicados.
Las instrucciones FOREIGN KEY, CHECK y REFERENCES en realidad no hacen nada. Se introdujeron solo por razones de compatibilidad, para facilitar la migración del código de otros servidores SQL y la ejecución de aplicaciones que crean tablas de referencia. Consulte la sección 1.9.4 Diferencias entre MySQL y ANSI SQL92.

A continuación se muestran ejemplos que muestran algunos de los usos del comando ALTER TABLE. El ejemplo comienza con la tabla t1, que se crea así:

Mysql> CREAR TABLA t1 (a INTEGER, b CHAR (10));

Para cambiar el nombre de una tabla de t1 a t2:

Mysql> ALTER TABLE t1 RENAME t2;

Para cambiar el tipo de una columna de INTEGER a TINYINT NOT NULL (manteniendo el mismo nombre) y cambiar el tipo de columna b de CHAR (10) a CHAR (20) y renombrarla de b a c:

Mysql> ALTER TABLE t2 MODIFICAR a TINYINT NO NULO, CAMBIAR b c CHAR (20);

Para agregar una nueva columna TIMESTAMP llamada d:

Mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Para agregar un índice a la columna dy convertir la columna a en la clave principal:

Mysql> ALTER TABLE t2 AÑADIR ÍNDICE (d), AÑADIR CLAVE PRIMARIA (a);

Para eliminar la columna c:

Mysql> ALTER TABLE t2 DROP COLUMN c;

Para agregar una nueva columna numérica AUTO_INCREMENT llamada c:

Mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);

Tenga en cuenta que la columna c está indexada ya que las columnas AUTO_INCREMENT deben indexarse y la columna c se declara NO NULL porque las columnas indexadas no pueden ser NULL.

Cuando agrega una columna AUTO_INCREMENT, los valores de esa columna se completan automáticamente con números secuenciales (al agregar registros). El primer número de secuencia se puede configurar ejecutando el comando SET INSERT_ID = # antes de ALTER TABLE o usando la opción de tabla AUTO_INCREMENT = #. Consulte la sección 5.5.6 SET Sintaxis.

Si la columna AUTO_INCREMENT para las tablas MyISAM no se cambia, el número de secuencia sigue siendo el mismo. Si elimina una columna AUTO_INCREMENT y luego agrega otra columna AUTO_INCREMENT, los números comenzarán de nuevo en 1.

En esta lección de SQL, veremos cómo usar el comando ALTER contra una tabla en una base de datos. El comando ALTER se utiliza principalmente para agregar, modificar y eliminar columnas a índices y restricciones de tabla en una base de datos relacional como Oracle, Sybase y SQL Server. Aunque ALTER no forma parte de la operación CRUD clásica, es uno de los comandos DDL importantes. Uno de los usos más comunes del comando ALTER en SQL es agregar y eliminar índices para mejorar el rendimiento de las consultas SQL SELECT. En este tutorial, veremos las diferentes formas de usar el comando ALTER. Usando el comando ALTER, puede agregar, editar o modificar tablas, vistas en bases de datos. Podemos agregar una nueva columna a la tabla, podemos cambiar el valor de la columna o podemos cambiar el nombre de la columna.

10 ejemplos de comando ALTER en SQL

Veamos algunos de los ejemplos más utilizados del comando ALTER en SQL. En esta sección, veremos cómo usar el comando ALTER para agregar, modificar y eliminar columnas existentes en una tabla.

Ejemplo 1: Cómo agregar una columna a una tabla existente usando ALTER en SQL:

Supongamos que tenemos una tabla llamada estudiante con el campo número_rollo, nombre_estudio, clase, materia y queremos agregar otra columna con las marcas obtenidas, como haría con comandosALTERAR.

MODIFICAR LA TABLA El estudiante AGREGAR puntuaciones_obtenidas Número (3);

Cuando este campo se agrega a la tabla, contendrá un valor predeterminado de cero.

Ejemplo 2: Cómo modificar una columna existente usando el comando ALTER en SQL :

Supongamos que ahora queremos cambiar el campo mark_obtained agregado anteriormente de un número a un tipo de datos decimal, podemos usar cambios de columna junto con un comando de modificación.

ALTER TABLE Alumno ALTER COLUMN marks_obtained DECIMAL (5, 2);

Ejemplo 3: Cómo eliminar una columna existente de una tabla usando el comando ALTER en SQL:

Ahora, si queremos eliminar una columna existente de la tabla, usamos DROP junto con el comando ALTER.

ALTER TABLE columna DROP del estudiante marks_obtained;

Ejemplo 4. Cómo soltar una clave primaria usando el comando ALTER en una base de datos :

Supongamos que hay una clave primaria llamada 'pk_roll_num' para la columna roll_number de la tabla Student y queremos eliminar esta restricción

ALTER TABLE RESTRICCIÓN DE CAÍDA DE ESTUDIANTES "pk_roll_num";

Ejemplo 5. Cómo agregar restricciones de clave primaria usando el comando ALTER en SQL:

Ahora queremos agregar una restricción de clave principal en la tabla Student llamada "pk_roll_num" para la columna roll_number ". Nosotros hacemos esto

ALTER TABLE Alumno ADD Restricción "pk_roll_num PRIMARY KEY (roll_number)

Ejemplo 6. Cómo restablecer una restricción de clave externa usando el comando ALTER:

Supongamos que hay una restricción de clave externa llamada 'fk_roll_num' en la columna roll_number 'de la tabla' Marks 'y queremos eliminar esta restricción

ALTER TABLE Marcas DROP CONSTRAINT "fk_roll_num";

Ejemplo 7: Cómo agregar restricciones de clave externa mediante el comando ALTER

Ahora queremos agregar una restricción de clave externa a la tabla Marks llamada 'fk_roll_num' para la columna roll_number. Nosotros haremos esto:

ALTER TABLE Marcas ADD Restricción "fk_roll_num FOREIGN KEY (roll_number) REFERENCIAS Estudiantes (roll_number);

Ejemplo 8: Cómo agregar restricciones de clave únicas mediante el comando ALTER

Ahora si queremos agregar único la clave de restricción de la tabla de Estudiantes llamada "unique_roll_no" para la columna roll_number. Nosotros haremos esto:

ALTER TABLE Estudiante AÑADIR CONSTRAINT unique_roll_no UNIQUE (roll_Number);

Ejemplo 9 : Cómo eliminar la clave de restricción única usando el comando ALTER

Ahora, si queremos soltar una clave de restricción única en la tabla de Estudiantes llamada "unique_roll_no" para la columna roll_number. Nosotros haremos esto:

MODIFICAR TABLA RESTRICCIÓN DE CAÍDA del estudiante unique_roll_no);

Ejemplo 10: Cómo agregar SNEC usando el comando ALTER en SQL

La sintaxis básica para agregar una restricción de verificación en una tabla es:

ALTER TABLE nombre_tabla AÑADIR CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

Se trata de ejemplos del comando ALTER en SQL. Hemos visto 10 formas diferentes de usar el comando ALTER en SQL. ALTER es uno de los más importantes a la hora de trabajar en cualquier base de datos como MySQL, Oracle o SQL Server, especialmente desde la línea de comandos. Dado que a menudo necesitamos agregar, modificar y eliminar columnas, índices y restricciones en la tabla.

Se pueden distinguir los siguientes niveles de restricciones de verificación:

nivel de atributo (columna),
nivel de tupla (fila),
nivel de relación (tabla).

Una restricción a nivel de columna verifica el valor de una sola columna, en otras palabras, una restricción de un tipo dado hace referencia solo a una columna de la tabla cuya definición contiene la restricción. Para dar un ejemplo de tal limitación, volvamos al diagrama de la empresa informática. En la tabla Producto, la columna de tipo puede contener uno de tres valores. Podemos prohibir ingresar cualquier otra información en esta columna usando la siguiente restricción:

COMPROBAR (escriba IN ("impresora", "pc", "laptop"))

Hagamos una digresión para familiarizarnos con la instrucción ALTER TABLE, que nos permitirá cambiar la estructura de una tabla sin tener que volver a crearla cada vez. Esto es aún más importante porque puede ser necesario un cambio en la estructura cuando la tabla ya contiene datos.

Puede agregar o eliminar columnas, valores predeterminados y restricciones mediante la instrucción ALTER TABLE.

Por el momento, estamos interesados en agregar una restricción en la columna de tipo, así que primero, aquí está la sintaxis del operador para agregar una restricción:

ALTER TABLE

AÑADIR RESTRICCIÓN;

Agreguemos ahora nuestra restricción y probemos cómo funciona.

ALTER TABLE Producto

AÑADIR RESTRICCIÓN chk_type CHECK (escriba IN ("pc", "laptop", "printer"));

Para asegurarnos de que la restricción funcione como se esperaba, intentemos agregar un nuevo tipo de modelo:

INSERT INTO Product VALUES ("A", 1122, "notebook");

Como era de esperar, recibiremos un error en respuesta:

La instrucción INSERT entró en conflicto con la restricción CHECK "chk_type". El conflicto ocurrió en la base de datos "aprender", tabla "dbo.product", columna "tipo". La instrucción se ha terminado.

(INSERT choca con la restricción CHECK "chk_type". Se produjo un conflicto en la base de datos "learn", tabla "dbo.product", columna "type". Esta declaración fue cancelada).

Como puede adivinar, la restricción de nivel de fila contiene varias referencias de columna. En este caso, la restricción se verifica para cada fila modificada por separado. La línea se puede agregar (o cambiar) si no se viola la restricción.

Como ejemplo, no permitamos que el fabricante Z lance cualquier otra cosa que no sean impresoras.

ALTER TABLE Producto

AÑADIR restricción chk_maker_Z CHECK ((maker = "Z" AND type = "printer") O maker "Z");

Entonces, la restricción verifica que el modelo en la tabla Producto debe ser una impresora del fabricante Z (fabricante = "Z" y tipo = "impresora") o cualquier otro fabricante (pero no Z).

Si intentamos agregar el modelo de PC del fabricante Z,

La manipulación de tablas es una de las actividades más comunes que realizan los administradores y programadores de bases de datos cuando trabajan con objetos de bases de datos. Esta sección describe en detalle cómo crear y modificar tablas.

El estándar ANSI es en cierto modo el denominador común más bajo para todos los fabricantes, aunque no todos los fabricantes implementan todas las características de la versión estándar de las declaraciones CREATE TABLE y ALTER TABLE. Sin embargo, el estándar ANSI es un formulario básico que se puede utilizar en todas las plataformas.

A su vez, las plataformas ofrecen varias extensiones y adiciones a las declaraciones ANSI CREATE TABLE y ALTER TABLE.

Como regla general, debe pensar detenidamente sobre el diseño de la mesa y cómo crearla. Este proceso se llama diseño de base de datos. El proceso de analizar la relación entre una tabla y sus propios datos y otras tablas en la base de datos se llama normalización. Recomendamos que los programadores y administradores de bases de datos estudien los principios de diseño y normalización antes de usar los comandos CREATE DATABASE.

Sintaxis de SQL 2003

Cuando ejecuta la instrucción CREATE TABLE de SQL 2003, se crea una tabla permanente o temporal en la base de datos. La sintaxis es la siguiente.

Palabras clave

TEMPORAL

Se declara una tabla permanente o temporal (TEMPORAL) con un ámbito local (LOCAL) o global (GLOBAL). Las tablas temporales locales son accesibles solo desde la sesión que las creó, y se eliminan automáticamente cuando finaliza la sesión que las creó. Las tablas temporales globales son accesibles desde todas las sesiones activas, pero se eliminan automáticamente cuando finaliza la sesión que las creó. No califique los nombres de tablas temporales con el nombre del esquema.

(atributos de tipo de datos column_name [,])

Se define una lista en la que se enumeran una o más columnas, separadas por comas, sus tipos de datos y atributos adicionales, como la posibilidad de nulos. Cada declaración de tabla debe incluir al menos una columna, para la cual puede especificar:

column_name

Se especifica el nombre de la columna. Debe ser un identificador válido desde el punto de vista de una política RDBMS específica. ¡El nombre debe ser significativo!

tipo de datos

Asocia un tipo de datos específico con una columna denominada column_name. Para los tipos de datos que le permiten especificar su longitud, existe una longitud de parámetro adicional, por ejemplo, VARCHAR (255). El tipo de datos debe ser válido en el RDBMS. Consulte el Capítulo 2 para obtener una descripción completa de los tipos de datos válidos y sus variantes para fabricantes específicos.

Vincula los atributos de restricción especificados a la columna. Se pueden especificar varios atributos para una sola columna denominada column_name. No se requieren comas. Los atributos ANSI típicos incluyen lo siguiente.

NO NULO

No se permiten valores NULL en la columna (o se permiten si se omite la cláusula NOT NULL). Cualquier instrucción INSERT y UPDATE que intente poner NULL en una columna NOT NULL fallará y se producirá una reversión.

Expresión DEFAULT

La columna usará el valor de la expresión si la instrucción INSERT o UPDATE no ingresa ningún valor. La expresión debe ser válida para el tipo de datos de la columna; por ejemplo, no se puede utilizar ningún carácter alfabético en una columna INTEGER. Una expresión puede ser una cadena o un literal numérico, pero también puede especificar una función del sistema o definida por el usuario. El estándar SQL 2003 permite las siguientes funciones del sistema en la cláusula DEFAULT: NULL, USER, CURRENTJJSER, SESSION_USER, SYSTEMJJSER, CURRENT_PATH, CURRENT_D AND TE, CURRENTJIME, LOCALTIME, CURRENTJIMESTAMP, LOCALTJMEST, ARRAY.

COLLATE collation_name

Se determina la colación utilizada, es decir, el orden de clasificación en la columna correspondiente. El nombre del mapeo depende de la plataforma. Si no se especifica un nombre de intercalación, el valor predeterminado es la intercalación por el juego de caracteres utilizado en la columna. SE COMPROBAN LAS REFERENCIAS Este parámetro determina si se comprueban las referencias en la columna REF especificada con la opción de alcance. La cláusula opcional ON DELETE determina si los valores de los registros a los que hace referencia el registro eliminado se establecerán en NULL o si la operación estará restringida.

CONSTRAINT nombre de restricción [tipo_restricción [restricción]]

El parámetro asigna una restricción a la columna y, opcionalmente, un nombre de restricción. Los tipos de restricciones se discuten en el Capítulo 2. Dado que la restricción está asociada con una columna específica, la declaración de restricción asume que esa columna es la única en la restricción. Una vez creada la tabla, la restricción se considera una restricción a nivel de tabla.

column_name

Una columna se define con opciones especiales como una opción de alcance, una opción predeterminada, una restricción de nivel de columna o una cláusula COLLATE. En muchas implementaciones, la cláusula WITH OPTIONS se limita a crear tablas con tipo.

LIKE nombre_tabla

La nueva tabla se crea con las mismas definiciones de columna que en la tabla existente table_name.

REFIS nombre_columna (SISTEMA GENERADO | USUARIO GENERADO DERIVADO)

Define una columna de identificador de objeto (OID) en tablas escritas. El identificador de objeto es necesario para la tabla raíz en la jerarquía de tablas. De acuerdo con este parámetro, la columna REF puede ser generada automáticamente por el sistema (SYSTEM GENERATED), especificada manualmente por el usuario al ingresar una fila (USER GENERATED), o en base a otra columna REF (DERIVED). El parámetro requiere que el atributo REFERENCES se incluya en la columna column_name.

RESTRICCIÓN tipo de restricción [nombre de restricción] [, ...]

Se asignan una o más restricciones a la tabla. Esta configuración difiere notablemente de las restricciones a nivel de columna en que se supone que las restricciones a nivel de columna se aplican solo a la columna a la que están asociadas. En el caso de restricciones a nivel de tabla, es posible asociar varias columnas con la restricción. Por ejemplo, en una tabla de ventas, es posible que desee declarar una restricción única en la clave concatenada store_id, order_id y order_date. Esto solo se puede hacer con una restricción a nivel de tabla. Para una discusión detallada de las restricciones, consulte el Capítulo 2.

OF type_name [table_definition]

Se declara que la tabla se basa en un tipo definido por el usuario predefinido. En esta situación, la tabla solo puede tener una columna para cada atributo de tipo estructurado, más una columna adicional definida en la cláusula REF IS. El tipo de datos REF se describe en detalle en la sección "instrucción CREATE / ALTER TOURE". Esta cláusula es incompatible con la cláusula LIKE table_name. Donde:

BAJO supertabla [definición / tablas]

Se declara una supertabla inmediata para la tabla actual en el mismo esquema (si existe). Opcionalmente, puede especificar una definición de tabla completa para la supertabla, llenándola con columnas, restricciones y similares.

ON COMMIT (PRESERVAR FILAS BORRAR FILAS)

La cláusula ON COMMIT PRESERVE ROWS conserva las filas de datos de la tabla temporal cuando se ejecuta la instrucción COMMIT. La cláusula ON COMMIT DELETE RO WS elimina todas las filas de datos en la tabla temporal cuando se ejecuta la instrucción COMMIT.

AÑADIR atributos de tipo de datos de column_name

Se agrega a la tabla una columna con el tipo de datos y los atributos adecuados.

ALTER nombre_columna SET DEFAULT valor_predeterminado

Se agrega un valor predeterminado a la columna (si no existe) o se cambia un valor existente.

ALTER nombre_columna DROP DEFAULT

El valor predeterminado se elimina por completo de la columna especificada.

ALTER nombre_columna AÑADIR SCOPE nombre_tabla

El alcance se agrega a la columna especificada. El alcance es una referencia a un tipo de datos personalizado.

ALTER nombre_columna DROP SCOPE

El alcance se elimina de la columna especificada. Las oraciones RESTRICT y CASCADE se explican al final de esta lista.

DROP COLUMN nombre_columna

La columna especificada se elimina de la tabla. Las oraciones RESTRICT y CASCADE se explican al final de esta lista.

AÑADIR table_constraint

Se agrega a la tabla una restricción con el nombre y las características especificados.

DROP CONSTRAINT nombre de restricción

La restricción existente se elimina de la tabla.

RESTRINGIR

Cuando se especifica esta cláusula, el RDBMS cancela el comando si encuentra objetos en la base de datos que dependen de este objeto.

Cuando se especifica esta cláusula, el RDBMS elimina todos los demás objetos que dependen de este objeto.