PostgreSQL – CREAR TABLA – Part 3

En PostgreSQL , la cláusula CREATE TABLE, como sugiere el nombre, se usa para crear nuevas tablas.

Syntax:
CREATE TABLE table_name (
    column_name TYPE column_constraint,
    table_constraint table_constraint
) INHERITS existing_table_name;

Analicemos la sintaxis anterior:

• Primero, define el nombre de la nueva tabla después de la cláusula CREATE TABLE. La palabra clave TEMPORARY es para crear una tabla temporal.
• A continuación, se debe enumerar el nombre de la columna, su tipo de datos y la restricción de la columna. Una tabla puede tener varias columnas separadas por una coma (,). La restricción de columna define reglas para la columna, por ejemplo, NOT NULL.
• Luego, después de la lista de columnas, se debe definir una restricción a nivel de tabla que defina reglas para los datos de la tabla.
• Después de eso, uno establece una tabla existente de la que hereda la nueva tabla. Significa que la nueva tabla contiene todas las columnas de la tabla existente y las columnas definidas en la instrucción CREATE TABLE. Esta es una extensión de PostgreSQL para SQL.

Ahora pasemos a un ejemplo.

Ejemplo:
En este ejemplo crearemos una nueva tabla llamada account que tiene las siguientes columnas con las restricciones correspondientes:

• id_usuario: clave principal
• nombre de usuario: único y no nulo
• contraseña – no nula
• correo electrónico: único y no nulo
• created_on – no nulo
• last_login – nulo

La siguiente instrucción crea la tabla de cuentas:

CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
);

La siguiente declaración crea la tabla de roles que consta de dos columnas: role_id y role_name:

CREATE TABLE role(
    role_id serial PRIMARY KEY,
    role_name VARCHAR (255) UNIQUE NOT NULL
);

La siguiente declaración crea la account_roles tabla que tiene tres columnas: user_id, role_id y grant_date:

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Examinemos la declaración anterior con más detalle.
La clave principal de la account_role tabla consta de dos columnas: user_id y role_id, por lo tanto, tenemos que usar la restricción de nivel de tabla de clave principal para definir la clave principal de la siguiente manera:

PRIMARY KEY (user_id, role_id)

Debido a que la user_id columna hace referencia a la user_id columna en la tabla de cuentas, necesitamos definir una restricción de clave externa para la user_id columna:

CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

Las referencias role_idcolumn a la role_id columna en la tabla de roles, también necesitamos definir una restricción de clave externa para la role_id columna:

CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION, 

Producción: