•Clasificar los objetos principales de la base de datos.
•Revisar la estructura de tabla.
•Mostrar los tipos de datos que están disponibles para las columnas
•Crear una tabla simple.
•Comprender cómo se crean las restricciones en el momento de la creación de la tabla
•Describir cómo funcionan los objetos de esquema.
NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________Objetos de Base de Datos.
Una base de datos Oracle puede contener varias estructuras de datos. Cada estructura debe tener bosquejarse en el diseño de base de datos para que se pueda crear durante la etapa de formación del desarrollo de base de datos.
•Tabla: Unidad básica de almacenamiento; compuesta por filas.
•Vista: Representa de forma lógica subjuegos de datos de una o más tablas.
•Secuencia: Genera valores numéricos.
•Índice: Mejora el rendimiento de algunas consultas.
•Sinónimo: Proporciona nombres alternativos a objetos.
Estructuras de Tablas Oracle.
•Las tablas se pueden crear en cualquier momento, incluso mientras los usuarios utilizan la base de datos.
•No es necesario especificar el tamaño de una tabla. El tamaño lo define en última instancia la cantidad de espacio asignado a toda la base de datos. Sin embargo, es importante estimar cuánto espacio utilizará una base de datos con el tiempo.
•La estructura de tabla se puede modificar online.
Nota: Hay más objetos de base de datos disponibles, los cuales analizamos en futuras publicaciones.
Las tablas y las columnas de base de datos se nombran de acuerdo con las reglas estándar de nomenclatura para cualquier objeto de base de datos Oracle:
•Los nombres de tabla y de columna deben comenzar por una letra y tener una longitud de 1 a 30 caracteres.
•Los nombres deben contener sólo los caracteres A–Z, a–z, 0–9, _ (subrayado), $ y # (caracteres legales, aunque no se recomienda su uso).
•Los nombres no deben duplicar el nombre de otro objeto que sea propiedad del mismo usuario de Oracle Server.
•Los nombres no deben ser palabras reservadas de Oracle Server.
Instrucciones de Nomenclatura
Utilice nombres descriptivos para tablas y otros objetos de base de datos.
Nota: Los nombres no son sensibles a mayúsculas/minúsculas. Por ejemplo, EMPLOYEES se trata igual que eMPloyees o eMpLOYEES.
_____________________________________________________________________________________
Sentencia CREATE TABLE.
Puede crear tablas para almacenar datos ejecutando la sentencia CREATE TABLE de SQL. Esta sentencia es una de las sentencias DDL (Data Definition Language), que son un subjuego de sentencias SQL utilizadas para crear, modificar o eliminar estructuras de base de datos Oracle. Estas sentencias tienen un efecto inmediato en la base de datos y también registran información en el diccionario de datos.
Para crear una tabla, un usuario debe tener el privilegio CREATE TABLE y un área de almacenamiento en la que crear objetos. El administrador de la base de datos utiliza sentencias de lenguaje de control de datos para otorgar privilegios a los usuarios (las sentencias DCL se tratan en una publicación posterior).
Sintaxis:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
Definición:
schema es igual que el nombre del propietario/usuario.
table es el nombre de la tabla.
DEFAULT expr especifica un valor por defecto si se omite un valor en la sentencia INSERT.
column es el nombre de la columna.
datatype es el tipo de datos y la longitud
de la columna.
Referencia a Tablas de Otro Usuario.
Esquema: es una recopilación de objetos. Los objetos de esquema son las estructuras lógicas que hacen referencia directa a los datos de una base de datos. Los objetos de esquema son tablas, vistas, sinónimos, secuencias, procedimientos almacenados, índices, agrupamientos y enlaces de base de datos.
Si una tabla no pertenece al usuario, el nombre del propietario debe ir como prefijo de la tabla. Por ejemplo, si hay esquemas denominados USERA y USERB, y ambos tienen una tabla EMPLOYEES, entonces si USERA desea acceder a la tabla EMPLOYEES que pertenece a USERB, debe poner como prefijo del nombre de tabla el nombre de esquema:
SELECT *
FROM userb.employees;
Si USERB desea acceder a la tabla EMPLOYEES propiedad de USERA, debe poner como prefijo del nombre de tabla el nombre de esquema:
SELECT *
FROM usera.employees;
---
Opción DEFAULT.
Opción DEFAULT.
Al definir una tabla, puede especificar que asigne un valor por defecto a una columna mediante la opción DEFAULT. Esta opción evita que se introduzcan valores nulos en las columnas si se inserta una fila sin un valor para la columna. El valor por defecto puede ser un literal, una expresión o una función SQL (como SYSDATE o USER), pero el valor no puede ser el nombre de otra columna o una pseudocolumna (como NEXTVAL o CURRVAL). La expresión por defecto debe corresponder con el tipo de datos de la columna.
Ejemplo:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_date DATE DEFAULT SYSDATE);
/*En el anterior ejemplo creamos la tabla dept con los campos: deptno, dname,loc y create_date. La columna create_date tiene un valor por defecto. Si no se proporciona un valor para una sentencia INSERT, se inserta automáticamente la fecha del sistema.
Una vez creada puede ver la estructura con: DESCRIBE. Eje: DESCRIBE dept*/
_____________________________________________________________________________________
Tipos de Datos.| Tipo de Datos | Descripción |
| VARCHAR2(size) | Datos de carácter
de longitud variable (Se debe especificar un tamaño máximo: el tamaño mínimo es 1; el tamaño máximo es 4.000.) |
| CHAR [(size)] | Datos de carácter
de longitud fija de bytes de tamaño de longitud (El tamaño mínimo y por defecto es 1; el tamaño máximo es 2.000.) |
| NUMBER [(p,s)] | Número con una
precisión p y una escala s (La precisión es el número total
de dígitos decimales y la escala el número de dígitos a la derecha del marcador decimal; la precisión puede ser de 1 a 38 y la escala, de –84 a 127.) |
| DATE | Valores de fecha y
de hora hasta el segundo más cercano entre el 1 de enero de 4712 a. C y el 31 de enero de 9999 d.C. |
| LONG | Datos de caracteres de longitud variable (hasta 2 GB) |
| CLOB | Datos de caracteres (hasta 4 GB) |
| RAW(size) | Datos binarios RAW de tamaño de longitud (Se debe especificar un tamaño máximo: el tamaño máximo es 2.000.) |
| LONG RAW | Datos binarios RAW de tamaño de longitud (Se debe especificar un tamaño máximo: el tamaño máximo es 2.000.) |
| BFILE | Datos binarios almacenados en un archivo externo (hasta 4 GB) |
| ROWID | Sistema numérico de base 64 que representa la única dirección de una fila en su tabla |
| TIMESTAMP | Permite que la hora se almacene como fecha con fracciones de segundo. Hay diversas variaciones de este tipo de datos. |
| INTERVAL YEAR TO MONTH | Permite que se almacene la hora como intervalo de años y meses. Se utiliza para representar la diferencia entre dos valores de fecha/hora en las que las únicas porciones significativas son el año y el mes. |
| INTERVAL DAY TO SECOND | Permite que se almacene la hora
como intervalo de días, horas, minutos y segundos Se utiliza para representar la diferencia precisa entre dos valores de fecha/hora. |
Directrices:
•No se copia una columna LONG si se crea una tabla mediante una subconsulta.
•Una columna LONG no se puede incluir en una cláusula GROUP BY o ORDER BY.
•Sólo se puede utilizar una columna LONG por tabla.
•No se pueden definir restricciones en una columna LONG.
•Es preferible que utilice una columna BLOB en lugar de una columna LONG.
•No se copia una columna LONG si se crea una tabla mediante una subconsulta.
•Una columna LONG no se puede incluir en una cláusula GROUP BY o ORDER BY.
•Sólo se puede utilizar una columna LONG por tabla.
•No se pueden definir restricciones en una columna LONG.
•Es preferible que utilice una columna BLOB en lugar de una columna LONG.
Nota: Con Oracle9i y versiones posteriores, están disponibles estos tipos de datos de fecha/hora. Para obtener información detallada sobre los tipos de datos de fecha/hora, consulte los temas “TIMESTAMP Datatype”, “INTERVAL YEAR TO MONTH Datatype” e “INTERVAL DAY TO SECOND Datatype” en Oracle SQL Reference.
_____________________________________________________________________________________
Tipos de Datos de Fechas.
TIMESTAMP[(fractional_seconds_precision)]
El tipo de datos TIMESTAMP es una extensión del tipo de datos DATE. Almacena el año, el mes y el día del tipo de datos DATE, más el valor de hora, minutos y segundos, así como el valor de fracciones de segundos. Este tipo de datos se utiliza para almacenar valores de hora precisos.
fractional_seconds_precision especifica opcionalmente el número de dígitos de la parte fraccional del campo de fecha/hora SECOND y puede ser un número del rango de 0 a 9. El valor por defecto es 6.
Sintaxis TIMESTAMP WITH TIME ZONE:
TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE
TIMESTAMP WITH TIME ZONE es una variante de TIMESTAMP que incluye el desplazamiento de la zona horaria en su valor. El desplazamiento de la zona horaria es la diferencia (en horas y minutos) entre la hora local y UTC (Hora Universal Coordinada, lo que antes se conocía como Hora del Meridiano de Greenwich). Este tipo de datos se utiliza para recopilar información de evaluación de fecha entre regiones geográficas.
Sintaxis TIMESTAMP WITH LOCAL TIME ZONE:
TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE es otra variante de TIMESTAMP que incluye el desplazamiento de la zona horaria en su valor. Se diferencia de TIMESTAMP WITH TIME ZONE en que los datos almacenados en la base de datos se normalizan según la zona horaria de la base de datos y el desplazamiento de zona horaria no se almacena como parte de los datos de la columna. Cuando los usuarios recuperan los datos, se devuelven según la zona horaria de la sesión local de los usuarios. El desplazamiento de zona horaria es la diferencia (en horas y minutos) entre la hora local y UTC. A diferencia de TIMESTAMP WITH TIME ZONE, puede especificar columnas del tipo TIMESTAMP WITH LOCAL TIME ZONE como parte de una clave primaria o única.
El tipo TIMESTAMP WITH LOCAL TIME ZONE es adecuado para aplicaciones de dos capas en las que desea mostrar fechas y horas mediante la zona horaria del sistema del cliente.
INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL YEAR TO MONTH almacena un período de tiempo mediante los campos YEAR y MONTH.
Utilice INTERVAL YEAR TO MONTH para representar la diferencia entre dos valores de fecha/hora en los que las únicas partes significativas son el año y el mes. Por ejemplo, puede utilizar este valor para definir un recordatorio para una fecha para la que faltan 120 meses o comprobar si han transcurrido 6 meses desde una fecha en particular.
year_precision es el número de dígitos del campo de fecha/hora YEAR. El valor por defecto de year_precision es 2.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
INTERVAL DAY TO SECOND almacena un período de tiempo en días, horas minutos y segundos.
Utilice INTERVAL DAY TO SECOND para representar la diferencia precisa entre dos valores de fecha/hora. Por ejemplo, puede utilizar este valor para definir un recordatorio para una hora para la que faltan 36 horas o para registrar la hora entre el inicio y el final de una carrera. Para representar períodos de tiempo extensos, que abarquen varios años, con mucha precisión, puede utilizar un valor grande para la parte de días.
day_precision es el número de dígitos del campo de fecha/hora DAY. Los valores aceptados van de 0 a 9. El valor por defecto es 2.
fractional_seconds_precision es el número de dígitos de la parte fraccional del campo de fecha/hora SECOND. Los valores aceptados van de 0 a 9. El valor por defecto es 6.
En pocas palabras una columna INTERVAL... almacena una intervalo de tiempo, osea un cantidad de tiempo, INTERVAL YEAR TO MONTH lo almacena en cantidad de años y/o meses; INTERVAL DAY TO SECOND lo almacena en cantidad de días y/o horas y/o minutes y/o segundos. Un uso común de estos tipos de datos es el de determina la fecha/hora exactas de un evento a partir de una fecha X, osea el valor almacenado en estos campos puede ser sumado a una fecha X para así determinar la fecha futura exacta.
Ejemplos:
CREATE TABLE date_data_types
(
c_date DATE,
c_timestamp TIMESTAMP,
c_timestamp_tz TIMESTAMP WITH TIME ZONE,
c_timestamp_ltz TIMESTAMP WITH LOCAL TIME ZONE,
c_interval_ym INTERVAL YEAR (3) TO MONTH,
c_interval_ds INTERVAL DAY (6) TO SECOND
);
/*Aquí creamos la tabla con solo campos tipo fecha.*/
/*Aquí creamos la tabla con solo campos tipo fecha.*/
---
DESC date_data_types;
/*Vemos la estructura de la tabla creada.*/
/*Vemos la estructura de la tabla creada.*/
---
INSERT INTO date_data_types --Eje 1
VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE,INTERVAL '6' YEAR,INTERVAL '8' DAY);
/*Este ejemplo inserta un nuevo registro en nuestra tabla; en los tres primeros campos insertamos la fecha del sistema(luego notaremos la diferencia), en la columna c_interval_ym insertamos una intervalo de 6 años, en la columna c_interval_ds insertamos un intervalo de 8 días(Luego mostramos un uso práctico para estos dos últimos campos).*/
---
INSERT INTO date_data_types (c_interval_ym,c_interval_ds) --Eje 2
VALUES(INTERVAL '15' MONTH,INTERVAL'69' HOUR);
/*Aquí solo insertamos en las columnas c_interval_ym y c_interval_ds(las demas toman NULL ya que las omitimos); en c_interval_ym insertamos 15 meses y en c_interval_ds 69 horas.*/
/*Aquí solo insertamos en las columnas c_interval_ym y c_interval_ds(las demas toman NULL ya que las omitimos); en c_interval_ym insertamos 15 meses y en c_interval_ds 69 horas.*/
---
INSERT INTO date_data_types (c_interval_ym,c_interval_ds) --Eje 3
VALUES(INTERVAL '3' YEAR,INTERVAL '180' MINUTE);
/*Aquí en la columna c_interval_ym insertamos 3 años y en c_interval_ds 180 minutos.*/
/*Aquí en la columna c_interval_ym insertamos 3 años y en c_interval_ds 180 minutos.*/
---
INSERT INTO date_data_types (c_interval_ym,c_interval_ds) --Eje 4
VALUES(INTERVAL '7-11' YEAR TO MONTH,INTERVAL '26 6' DAY TO HOUR);
/*Ahora vemos como en c_interval_ym insertamos 7 años y 11 meses y en c_interval_ds 26 y 6 horas; Notar como varia ligeramente la Sintaxis.*/
/*Ahora vemos como en c_interval_ym insertamos 7 años y 11 meses y en c_interval_ds 26 y 6 horas; Notar como varia ligeramente la Sintaxis.*/
---
INSERT INTO date_data_types (c_interval_ym,c_interval_ds) --Eje 5
VALUES(INTERVAL '4-3' YEAR TO MONTH,INTERVAL '6 6:45' DAY TO MINUTE);
/*Acá insertamos 4 años y 3 meses en c_interval_ym y 6 días, 6 horas y 45 minutos en c_interval_ds; Notar Sintaxis.*/
/*Acá insertamos 4 años y 3 meses en c_interval_ym y 6 días, 6 horas y 45 minutos en c_interval_ds; Notar Sintaxis.*/
---
INSERT INTO date_data_types (c_interval_ym,c_interval_ds) --Eje 6
VALUES(INTERVAL '2' YEAR,INTERVAL '2 2:15:27' DAY TO SECOND);
/*Finalmente, aquí insertamos 2 años en c_interval_ym y 2 días, 2 horas, 15 minutos y 27 segundos en c_interval_ds; Notar Sintaxis.*/
/*Finalmente, aquí insertamos 2 años en c_interval_ym y 2 días, 2 horas, 15 minutos y 27 segundos en c_interval_ds; Notar Sintaxis.*/
---
Output de los Ejemplos anteriores:
Nota: si recibe error tratando de insertar en el campo: c_interval_ds, debe cambiar los ':' por '.', Eje:
INTERVAL '2 2.15.27' DAY TO SECOND.
---
Uso de INTERVAL YEAR TO MONTH y INTERVAL DAY TO SECOND.
Para ilustrar esto mejor, lo haremos con un ejemplo basado en las inserciones anteriores:
SELECT TO_CHAR(SYSDATE, 'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA",
c_interval_ym,
TO_CHAR(SYSDATE+c_interval_ym, 'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA MAS C_INTERVAL_YM",
'||',
TO_CHAR(SYSDATE, 'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA 2",
c_interval_ds,
TO_CHAR(SYSDATE+c_interval_ds,'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA MAS C_INTERVAL_DS"
FROM date_data_types;
---
Mi Output:
Output de los Ejemplos anteriores:
/*Eje 1: Insertamos SYSDATE en los tres primero registros; en la columna c_date notamos que la fecha insertada luce: dd-mon-yy, esto debido a que esa columna es tipo DATE; en c_timestamp luce: dd-mon-yy hh.mm.ss.000000000 AM, esto es porque el tipo TIMESTAMP almacena todo esto; en la columna c_timestamp_tz luce: dd-mon-yy hh.mm.ss.000000000 AM ZONA HORARIA, TIMESTAMP WITH TIME ZONE muestra lo mismo que TIMESTAMP mas la zona horaria; en la colomna c_timestamp_ltz se muestra: dd-mon-yy hh.mm.ss.000000000 AM, luce igual que TIMESTAMP ya que TIMESTAMP WITH LOCAL TIME ZONE asume la zona horaria por defecto del sistema; en la columna c_interval_ys se insertó: INTERVAL '6' YEAR y luce: +06-00, esto es 6 años 0 meses; en la columna c_inteval_ds se insertó: INTERVAL '8' DAY y luce: +08 00:00:00.000000, esto es 8 días, 0 horas, 0 minutos, 0 segundos.*/
---
/*Eje 2: En la Columna c_interval_ys se insertó: INTERVAL '15' MONTH y luce: +01-03, esto es 1 año y 3 meses= 15 meses; en c_interval_ds se insertó: INTERVAL'69' HOUR y luce: +02 21:00:00.000000, esto es 2 días, 21 minutos y 0 segundos=69 horas.*/
/*Eje 2: En la Columna c_interval_ys se insertó: INTERVAL '15' MONTH y luce: +01-03, esto es 1 año y 3 meses= 15 meses; en c_interval_ds se insertó: INTERVAL'69' HOUR y luce: +02 21:00:00.000000, esto es 2 días, 21 minutos y 0 segundos=69 horas.*/
---
/*Eje 3: En la Columna c_interval_ys se insertó: INTERVAL '3' YEAR y luce: +03-00, esto es 3 Años y 0 meses; en c_interval_ds se insertó: INTERVAL '180' MINUTE y luce: +00 03:00:00.000000, esto es 0 días, 3 horas, 0 minutos y 0 segundos= 180 minutos.*/
---
/*Eje 4: En la Columna c_interval_ys se insertó: INTERVAL '7-11' YEAR TO MONTH y luce: +07-11, esto es 7 años y 11 meses; en c_interval_ds se insertó: INTERVAL '26 6' DAY TO HOUR y luce: +26 06:00:00.000000, esto es 26 días, 6 horas, 0 minutos y 0 segundos.*/
---
/*Eje 5: En la Columna c_interval_ys se insertó: INTERVAL '4-3' YEAR TO MONTH y luce: +04-03, esto es 4 años y 3 meses; en c_interval_ds se insertó: INTERVAL '6 6:45' DAY TO MINUTE y luce: +06 06:45:00.000000, esto es 6 días, 6 horas, 45 minutos 0 segundos.*/
---
/*Eje 6: En la Columna c_interval_ys se insertó: INTERVAL '2' YEAR y luce: +02-00, esto es 2 años, 0 meses; en c_interval_ds se insertó: INTERVAL '2 2:15:27' DAY TO SECOND y luce: +02 02:15:27.000000, esto es 2 días, 2 horas, 15 minutos y 27 segundos.*/
---Nota: si recibe error tratando de insertar en el campo: c_interval_ds, debe cambiar los ':' por '.', Eje:
INTERVAL '2 2.15.27' DAY TO SECOND.
---
Uso de INTERVAL YEAR TO MONTH y INTERVAL DAY TO SECOND.
Para ilustrar esto mejor, lo haremos con un ejemplo basado en las inserciones anteriores:
SELECT TO_CHAR(SYSDATE, 'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA",
c_interval_ym,
TO_CHAR(SYSDATE+c_interval_ym, 'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA MAS C_INTERVAL_YM",
'||',
TO_CHAR(SYSDATE, 'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA 2",
c_interval_ds,
TO_CHAR(SYSDATE+c_interval_ds,'dd-mon-yy hh:mm:ss AM') AS "FECHA Y HORA MAS C_INTERVAL_DS"
FROM date_data_types;
---
Mi Output:
_____________________________________________________________________________________
Restricciones de Integridad de Datos
Oracle Server utiliza restricciones para evitar la entrada de datos no válidos en las tablas.
Todas las restricciones se almacenan en el diccionario de datos. Resulta fácil hacer referencia a las tablas si se les pone un nombre significativo. Los nombres de restricción deben seguir las reglas estándar de nomenclatura de objetos. Si no le pone nombre a una restricción, Oracle Server generará uno con el formato SYS_Cn, donde n es un entero que hace que el nombre de restricción sea único.
Las restricciones se pueden definir en el momento de la creación de la tabla o después de su creación. Si es al momento de la creación de la tabla podemos definirlas a nivel de columna o de tabla.
Las restricciones de nivel de tabla se definen al final de la definición de la tabla y deben hacer referencia a la columna o a las columnas a las que pertenece la restricción en un juego de paréntesis.
Las restricciones NOT NULL se deben definir en el nivel de columna.
Puede utilizar restricciones para:
•Forzar reglas en los datos de una tabla siempre que se inserte, se actualice o se suprima una fila de dicha tabla. Se debe satisfacer la restricción para que la operación sea correcta.
•Evitar la supresión de una tabla si hay dependencias de otras tablas.
•Proporcionar reglas para herramientas Oracle, como Oracle Developer.
•Forzar reglas en los datos de una tabla siempre que se inserte, se actualice o se suprima una fila de dicha tabla. Se debe satisfacer la restricción para que la operación sea correcta.
•Evitar la supresión de una tabla si hay dependencias de otras tablas.
•Proporcionar reglas para herramientas Oracle, como Oracle Developer.
| Restricción | Descripción |
| NOT NULL | Especifica que la columna no puede contener valores nulos. |
| UNIQUE | Especifica una
columna o una combinación de columnas cuyos valores deben ser únicos para todas las filas de la tabla. |
| PRIMARY KEY | Identifica de manera única cada fila de la tabla. |
| FOREIGN KEY | Establece y fuerza
una relación de clave ajena entre la columna y una columna de la tabla referenciada. |
| CHECK | Especifica una condición que debe ser verdadera. |
Sintaxis:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
A nivel de Columna:
column [CONSTRAINT constraint_name] constraint_type,
A nivel de Tabla:
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
Definición:
schema es igual que el nombre del propietario.
table es el nombre de la tabla.
DEFAULT expr especifica un valor por defecto si se omite un valor en la sentencia INSERT.
column es el nombre de la columna.
datatype es el tipo de datos y la longitud de la columna.
column_constraint en una restricción de integridad como parte de la definición de columna.
table_constraint en una restricción de integridad como parte de la definición de tabla.
_____________________________________________________________________________________
Definición de Restricciones.
Definición de Restricciones.
Las restricciones se suelen crear al mismo tiempo que la tabla. Las restricciones se pueden agregar a una tabla después de su creación y también se pueden desactivar temporalmente.
Restricción NOT NULL.
La restricción NOT NULL asegura que la columna no contenga valores nulos. Las columnas sin la restricción NOT NULL pueden contener valores nulos por defecto. Las restricciones NOT NULL se deben definir en el nivel de columna.
Restricción UNIQUE.
La restricción de integridad de claves UNIQUE requiere que todos los valores de una columna o de un juego de columnas (clave) sean únicos; es decir, no puede haber dos filas de una tabla que tengan valores duplicadas en una columna o en un juego de columnas específicos. La columna (o el juego de columnas) incluida en la definición de la restricción de clave UNIQUE se denomina clave única. Si la restricción UNIQUE abarca más de una columna, ese grupo de columnas se denomina clave única compuesta.
Las restricciones UNIQUE permiten la entrada de valores nulos a menos que defina también restricciones NOT NULL para las mismas columnas. De hecho, cualquier número de filas puede incluir valores nulos para columnas sin restricciones NOT NULL porque los valores nulos no se consideran igual a ninguna otra cosa. Un valor nulo en una columna (o en todas las columnas de una clave UNIQUE compuesta) siempre satisface una restricción UNIQUE.
Nota: Debido al mecanismo de búsqueda para restricciones UNIQUE en más de una columna, no puede haber valores idénticos en las columnas no nulas de una restricción de clave UNIQUE compuesta parcialmente nula.
Restricción PRIMARY KEY.
La restricción PRIMARY KEY crea una clave primaria para la tabla. Sólo se puede crear una clave primaria para cada tabla. La restricción PRIMARY KEY es una columna o un juego de columnas que identifican de forma única cada fila de una tabla. Esta restricción fuerza la unicidad de la columna o de la combinación de columnas y asegura que ninguna columna que forme parte de la clave primaria pueda contener un valor nulo.
Se puede decir que la restricción PRIMARY KEY es una combinación de NOT NULL y UNIQUE.
Nota: Como la unicidad es parte de la definición de restricción de clave primaria, Oracle Server fuerza la unicidad creando implícitamente un índice único en la columna o columnas de clave primaria.
Restricción FOREIGN KEY.
La restricción FOREIGN KEY (o de integridad referencial) designa una columna o una combinación de columnas como clave ajena y establece una relación entre una clave primaria o una clave única en la misma tabla o en una tabla diferente.
Las restricciones FOREIGN KEY se pueden definir en los niveles de restricción de columna o de tabla. Una clave ajena compuesta se debe crear mediante la definición de nivel de tabla.
La clave ajena también se puede definir en el nivel de columna, siempre que la restricción se base en una sola columna. La sintaxis difiere en que no aparecen las palabras clave FOREIGN KEY.
Directrices:
•Un valor de clave ajena debe corresponder con un valor existente de la tabla principal o ser NULL(Si dicha columna permite NULL).
•Las claves ajenas se basan en valores de datos y son punteros puramente lógicos, más que físicos.
Mas Detalles:
La clave ajena se define en la tabla secundaria y la tabla que contiene la tabla referenciada es la tabla principal. La clave ajena se define mediante una combinación de las siguientes palabras clave:
•FOREIGN KEY se utiliza para definir la columna en la tabla secundaria en el nivel de restricción de tabla.
•REFERENCES identifica la tabla y la columna en la tabla principal.
•ON DELETE CASCADE indica que cuando se suprime una fila en la tabla principal, las filas dependientes en la tabla secundaria también se suprimen.
•ON DELETE SET NULL convierte valores de clave ajena en valores nulos cuando se elimina el valor principal.
El comportamiento por defecto se denomina regla de restricción, que desautoriza la actualización o la supresión de datos referenciados.
Sin las opciones ON DELETE CASCADE u ON DELETE SET NULL, la fila de la tabla principal no se puede suprimir si se hace referencia a ella en la tabla secundaria.
Restricción CHECK.
La restricción CHECK define una condición que debe satisfacer cada fila La condición puede utilizar las mismas construcciones que las condiciones de consulta, con las siguientes excepciones:
•Referencias a las pseudocolumnas CURRVAL, NEXTVAL, LEVEL y ROWNUM.
•Llamadas a las funciones SYSDATE, UID, USER y USERENV.
•Consultas que hagan referencia a otros valores de otras filas.
Una sola columna puede tener varias restricciones CHECK que hagan referencia a la columna en su definición. No hay límite para el número de restricciones CHECK que se pueden definir en una columna.
Las restricciones CHECK se pueden definir en los niveles de columna o de tabla.
Ejemplos:
CREATE TABLE nacionalidades -- Eje 1
( nacionalidad_id NUMBER(4) PRIMARY KEY,
descripcion VARCHAR2(35) NOT NULL UNIQUE,
pais VARCHAR2(15) NOT NULL UNIQUE,
continente VARCHAR2(15) NOT NULL
);
/*Aquí creamos la tabla nacionalidades; al campo nacionalidad_id le creamos una restricción PRIMARY KEY, a los campos descripción y país le creamos restricciones NOT NULL e UNIQUE y al campo continente le creamos una NOT NULL; como no usamos la palabra CONSTRAINT los nombre se lo asigna el Oracle Server y por defecto tienen el formato: SYS_Cn.*/
---
DESC nacionalidades;
---
CREATE TABLE cliente -- Eje 2
( c_id NUMBER(8) CONSTRAINT pk_c_id PRIMARY KEY,
c_nombre VARCHAR2(35) CONSTRAINT nn_c_nombre NOT NULL,
c_edad NUMBER(3) CHECK(c_edad BETWEEN 18 AND 69),
c_sexo CHAR(1) CONSTRAINT ck_c_sexo CHECK(c_sexo IN ('F','M')),
c_numero VARCHAR2(15) CONSTRAINT nn_c_numero NOT NULL,
c_correo VARCHAR2(35) CONSTRAINT un_c_correo UNIQUE,
nacionalidad_id NUMBER(4)
CONSTRAINT fk_c_nacional
REFERENCES nacionalidades(nacionalidad_id),
c_direccion VARCHAR2(60) UNIQUE);
/* En este ejemplo creamos la tabla cliente; al campo c_id le creamos una restricción PRIMARY KEY, notar el uso de la palabra CONSTRAINT para así establecer un nombre: pk_c_id; al campo c_name le establecemos una restricción NOT NULL con nombre: nn_c_nombre; al campo c_edad le establecemos una restricción CHECK para limitar un rango de 18 a 69 años, como no establecemos nombre el Oracle Server le crea uno; al campo c_sexo le creamos un CHECK llamado: ck_c_sexo para así limitar los valores F o M; al campo c_numero le creamos una NOT NULL con nombre: nn_c_numero; al campo c_correo le creamos una restricción UNIQUE con nombre: un_c_correo; al campo nacionalidad_id le creamos una restriction de integridad referencial o FOREIGN KEY con nombre: fk_c_nacional para así limitar este campo a los datos previamente insertados en el campo con el mismo nombre de la tabla nacionalidades; finalmente al campo c_direccion le creamos una UNIQUE, esta evita que haya dos clientes con la misma dirección. Todos los CONSTRAINTS creados están en el nivel de columna.*/
---
DESC cliente;
---
CREATE TABLE cliente2 -- Eje 3
( c_id NUMBER(8),
c_nombre VARCHAR2(35) NOT NULL,
c_edad NUMBER(3),
c_sexo CHAR(1),
c_numero VARCHAR2(15) NOT NULL,
c_correo VARCHAR2(35),
nacionalidad_id NUMBER(4),
c_direccion VARCHAR2(60),
CONSTRAINT pk_c_id2 PRIMARY KEY(c_id),
CONSTRAINT ck_c_edad2 CHECK(c_edad BETWEEN 18 AND 69),
CONSTRAINT ck_c_sexo2 CHECK(c_sexo IN ('F','M')),
CONSTRAINT un_c_correo2 UNIQUE(c_correo),
CONSTRAINT fk_c_nacional2 FOREIGN KEY(nacionalidad_id)
REFERENCES nacionalidades(nacionalidad_id),
CONSTRAINT un_c_direccion UNIQUE(c_direccion)
);
/*En este ejemplo creamos la tabla cliente2. esta tabla es idéntica a la tabla cliente; la creamos para mostrar como crear restricciones a nivel de tabla, notar que los CONSTRAINTS NOT NULL tienen que ser creados a nivel de columna, también ven como establezco nombres diferentes a las restricciones ya que el nombre de cada restricción tiene que ser único.*/
_____________________________________________________________________________________
Creando tablas con SubConsultas.
Creación de una Tabla a Partir de Filas de Otra Tabla.
( nacionalidad_id NUMBER(4) PRIMARY KEY,
descripcion VARCHAR2(35) NOT NULL UNIQUE,
pais VARCHAR2(15) NOT NULL UNIQUE,
continente VARCHAR2(15) NOT NULL
);
/*Aquí creamos la tabla nacionalidades; al campo nacionalidad_id le creamos una restricción PRIMARY KEY, a los campos descripción y país le creamos restricciones NOT NULL e UNIQUE y al campo continente le creamos una NOT NULL; como no usamos la palabra CONSTRAINT los nombre se lo asigna el Oracle Server y por defecto tienen el formato: SYS_Cn.*/
---
DESC nacionalidades;
---
CREATE TABLE cliente -- Eje 2
( c_id NUMBER(8) CONSTRAINT pk_c_id PRIMARY KEY,
c_nombre VARCHAR2(35) CONSTRAINT nn_c_nombre NOT NULL,
c_edad NUMBER(3) CHECK(c_edad BETWEEN 18 AND 69),
c_sexo CHAR(1) CONSTRAINT ck_c_sexo CHECK(c_sexo IN ('F','M')),
c_numero VARCHAR2(15) CONSTRAINT nn_c_numero NOT NULL,
c_correo VARCHAR2(35) CONSTRAINT un_c_correo UNIQUE,
nacionalidad_id NUMBER(4)
CONSTRAINT fk_c_nacional
REFERENCES nacionalidades(nacionalidad_id),
c_direccion VARCHAR2(60) UNIQUE);
/* En este ejemplo creamos la tabla cliente; al campo c_id le creamos una restricción PRIMARY KEY, notar el uso de la palabra CONSTRAINT para así establecer un nombre: pk_c_id; al campo c_name le establecemos una restricción NOT NULL con nombre: nn_c_nombre; al campo c_edad le establecemos una restricción CHECK para limitar un rango de 18 a 69 años, como no establecemos nombre el Oracle Server le crea uno; al campo c_sexo le creamos un CHECK llamado: ck_c_sexo para así limitar los valores F o M; al campo c_numero le creamos una NOT NULL con nombre: nn_c_numero; al campo c_correo le creamos una restricción UNIQUE con nombre: un_c_correo; al campo nacionalidad_id le creamos una restriction de integridad referencial o FOREIGN KEY con nombre: fk_c_nacional para así limitar este campo a los datos previamente insertados en el campo con el mismo nombre de la tabla nacionalidades; finalmente al campo c_direccion le creamos una UNIQUE, esta evita que haya dos clientes con la misma dirección. Todos los CONSTRAINTS creados están en el nivel de columna.*/
---
DESC cliente;
---
CREATE TABLE cliente2 -- Eje 3
( c_id NUMBER(8),
c_nombre VARCHAR2(35) NOT NULL,
c_edad NUMBER(3),
c_sexo CHAR(1),
c_numero VARCHAR2(15) NOT NULL,
c_correo VARCHAR2(35),
nacionalidad_id NUMBER(4),
c_direccion VARCHAR2(60),
CONSTRAINT pk_c_id2 PRIMARY KEY(c_id),
CONSTRAINT ck_c_edad2 CHECK(c_edad BETWEEN 18 AND 69),
CONSTRAINT ck_c_sexo2 CHECK(c_sexo IN ('F','M')),
CONSTRAINT un_c_correo2 UNIQUE(c_correo),
CONSTRAINT fk_c_nacional2 FOREIGN KEY(nacionalidad_id)
REFERENCES nacionalidades(nacionalidad_id),
CONSTRAINT un_c_direccion UNIQUE(c_direccion)
);
/*En este ejemplo creamos la tabla cliente2. esta tabla es idéntica a la tabla cliente; la creamos para mostrar como crear restricciones a nivel de tabla, notar que los CONSTRAINTS NOT NULL tienen que ser creados a nivel de columna, también ven como establezco nombres diferentes a las restricciones ya que el nombre de cada restricción tiene que ser único.*/
_____________________________________________________________________________________
Creando tablas con SubConsultas.
Creación de una Tabla a Partir de Filas de Otra Tabla.
Un segundo método para crear tablas es aplicar la cláusula AS subquery, que crea la tabla e inserta las filas devueltas por la subconsulta.
Sintaxis:
column es el nombre de la columna, el valor por defecto y la restricción de integridad.
subquery es la sentencia SELECT que define el juego de filas que se insertarán en la nueva tabla.
Directrices:
•La tabla se crea con los nombres de columna especificados y las filas recuperadas por la sentencia SELECT se insertan en la tabla.
•La definición de columna puede contener sólo el nombre de columna y el valor por defecto.
•Si se proporcionan especificaciones de columna, el número de columnas debe ser igual que el número de columnas de la lista SELECT de la subconsulta.
•Si no se proporcionan especificaciones de columna, los nombres de columna de la tabla son los mismos que los nombres de columna de la subconsulta.
•Las reglas de integridad no se transfieren a la nueva tabla; sólo se transfieren las definiciones de tipos de datos de columna y las restricciones NOT NULL.
Ejemplos:
CREATE TABLE copia_cliente
AS SELECT *
FROM cliente;
/*Aquí creamos la tabla: copia_cliente con los mismos campos y estructura de la ya creada: cliente; si vemos su estructura con DESC copia_cliente; notamos que copió los CONSTRAINTS NOT NULL y que los campos tienen los mismo nombres. Adicionalmente se copia también la data almacenada en la tabla de ejemplo, en mi caso no copio nada ya que mi tabla cliente estaba limpia.*/
---
CREATE TABLE empleados
AS
SELECT employee_id AS num_empleado,
first_name AS nombre,
salary AS salario,
email AS correo,
phone_number AS numero,
hire_date AS fecha_contrato
FROM employees;
/*En este ejemplo creamos la tabla empleados con algunos de los campos de la tabla employees, en este caso los datos almacenados en las columnas seleccionas de la tabla employees ahora están también en empleados; notar como pongo un alias para cada campo en la subconsulta, dicho alias es el nombre de columna para cada una en la tabla empleados.*/
---
DESC empleados;
---
CREATE TABLE copia_empleados
( num_empleado,
nombre,
salario,
correo,
numero,
fecha_contratacion,
CONSTRAINT pk_id_nombre PRIMARY KEY(num_empleado,nombre),
CONSTRAINT un_correo_numero UNIQUE(numero,correo)
)
AS
SELECT employee_id,
first_name||' '||last_name,
salary,
email,
phone_number,
hire_date
FROM employees;
/*En este ejemplo vemos otra manera de crear una tabla basada en una subconsulta, como vemos los nombres de cada campo son especificados antes de la subconsulta; es bueno tener precaución de que cada campo este en el order deseado y que coincida con el de la subconsulta; de paso vemos como crear restricciones basadas en mas de una columna.*/
_____________________________________________________________________________________
Borrado de una Tabla
La sentencia DROP TABLE elimina la definición de una tabla Oracle. Al borrar una tabla, la base de datos pierde todos los datos de la tabla y todos los índices asociados a ella.
Sintaxis:
DROP TABLE table;
En la sintaxis, table es el nombre de la tabla.
Directrices:
•Se suprimen todos los datos de la tabla.
•Las vistas y los sinónimos permanecen, pero no son válidos.
•Se validan todas las transacciones pendientes.
•Sólo puede eliminar un tabla su creador o un usuario con el privilegio DROP ANY TABLE.
Nota: La sentencia DROP TABLE, una vez ejecutada, es irreversible. Oracle Server no cuestiona la acción de emitir la sentencia DROP TABLE. Si es propietario de esa tabla o tiene un privilegio de alto nivel, la tabla se eliminará inmediatamente. Como sucede con todas las sentencias DDL, DROP TABLE se valida automáticamente.
Sintaxis:
CREATE TABLE table
[(column, column...)]
AS subquery;
Definición:
table es el nombre de la nueva tabla a crear.Definición:
column es el nombre de la columna, el valor por defecto y la restricción de integridad.
subquery es la sentencia SELECT que define el juego de filas que se insertarán en la nueva tabla.
Directrices:
•La tabla se crea con los nombres de columna especificados y las filas recuperadas por la sentencia SELECT se insertan en la tabla.
•La definición de columna puede contener sólo el nombre de columna y el valor por defecto.
•Si se proporcionan especificaciones de columna, el número de columnas debe ser igual que el número de columnas de la lista SELECT de la subconsulta.
•Si no se proporcionan especificaciones de columna, los nombres de columna de la tabla son los mismos que los nombres de columna de la subconsulta.
•Las reglas de integridad no se transfieren a la nueva tabla; sólo se transfieren las definiciones de tipos de datos de columna y las restricciones NOT NULL.
Ejemplos:
CREATE TABLE copia_cliente
AS SELECT *
FROM cliente;
/*Aquí creamos la tabla: copia_cliente con los mismos campos y estructura de la ya creada: cliente; si vemos su estructura con DESC copia_cliente; notamos que copió los CONSTRAINTS NOT NULL y que los campos tienen los mismo nombres. Adicionalmente se copia también la data almacenada en la tabla de ejemplo, en mi caso no copio nada ya que mi tabla cliente estaba limpia.*/
---
CREATE TABLE empleados
AS
SELECT employee_id AS num_empleado,
first_name AS nombre,
salary AS salario,
email AS correo,
phone_number AS numero,
hire_date AS fecha_contrato
FROM employees;
/*En este ejemplo creamos la tabla empleados con algunos de los campos de la tabla employees, en este caso los datos almacenados en las columnas seleccionas de la tabla employees ahora están también en empleados; notar como pongo un alias para cada campo en la subconsulta, dicho alias es el nombre de columna para cada una en la tabla empleados.*/
---
DESC empleados;
---
CREATE TABLE copia_empleados
( num_empleado,
nombre,
salario,
correo,
numero,
fecha_contratacion,
CONSTRAINT pk_id_nombre PRIMARY KEY(num_empleado,nombre),
CONSTRAINT un_correo_numero UNIQUE(numero,correo)
)
AS
SELECT employee_id,
first_name||' '||last_name,
salary,
email,
phone_number,
hire_date
FROM employees;
/*En este ejemplo vemos otra manera de crear una tabla basada en una subconsulta, como vemos los nombres de cada campo son especificados antes de la subconsulta; es bueno tener precaución de que cada campo este en el order deseado y que coincida con el de la subconsulta; de paso vemos como crear restricciones basadas en mas de una columna.*/
_____________________________________________________________________________________
Borrado de una Tabla
La sentencia DROP TABLE elimina la definición de una tabla Oracle. Al borrar una tabla, la base de datos pierde todos los datos de la tabla y todos los índices asociados a ella.
Sintaxis:
DROP TABLE table;
En la sintaxis, table es el nombre de la tabla.
Directrices:
•Se suprimen todos los datos de la tabla.
•Las vistas y los sinónimos permanecen, pero no son válidos.
•Se validan todas las transacciones pendientes.
•Sólo puede eliminar un tabla su creador o un usuario con el privilegio DROP ANY TABLE.
Nota: La sentencia DROP TABLE, una vez ejecutada, es irreversible. Oracle Server no cuestiona la acción de emitir la sentencia DROP TABLE. Si es propietario de esa tabla o tiene un privilegio de alto nivel, la tabla se eliminará inmediatamente. Como sucede con todas las sentencias DDL, DROP TABLE se valida automáticamente.
Ejemplos:
DROP TABLE copia_empleados;
/*Borra la tabla copia_empleados*/
---
DROP TABLE empleados;
/*Borra la tabla empleados*/
---
DROP TABLE cliente2;
/*Borra la tabla cliente2*/
_____________________________________________________________________________________
_____________________________________________________________________________________
