How to create and constrain tables in a database - AnalyticDB

Les tables d'AnalyticDB for PostgreSQL fonctionnent comme celles des bases de données relationnelles, avec une différence majeure : les lignes sont distribuées sur plusieurs nœuds de calcul. La politique de distribution de chaque table détermine la répartition des lignes.

Créer une table standard

Utilisez la commande CREATE TABLE pour définir une table. Lors de la création, spécifiez :

Les noms des colonnes et leurs types de données
Les contraintes
La distribution de la table
Le modèle de stockage
La stratégie de partitionnement

Syntaxe :

CREATE TABLE table_name (
  [ { column_name data_type [ DEFAULT default_expr ]   -- Column definition
     [column_constraint [ ... ]]                        -- Column-level constraint
  ]
     | table_constraint                                 -- Table-level constraint
  ])
  [ WITH ( storage_parameter=value [, ... ] ) ]         -- Storage model
  [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]  -- Distribution key
  [ partition clause]                                   -- Partitioning strategy

Exemple :

L'exemple suivant crée une table sales en utilisant trans_id comme clé de distribution et un partitionnement par plage basé sur la date.

CREATE TABLE sales (
  trans_id int,
  date date,
  amount decimal(9,2),
  region text)
  DISTRIBUTED BY (trans_id)
  PARTITION BY RANGE(date)
  (start (date '2018-01-01') inclusive
   end (date '2019-01-01') exclusive every (interval '1 month'),
   default partition outlying_dates);

Créer une table temporaire

Les tables temporaires stockent des données intermédiaires pendant toute la durée d'une session ou d'une transaction. Par défaut, le système supprime automatiquement une table temporaire à la fin de la session.

Syntaxe :

CREATE TEMPORARY TABLE table_name(...)
    [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]

Utilisez la clause ON COMMIT pour contrôler le sort de la table à la fin de la transaction en cours :

Option	Comportement
`PRESERVE ROWS`	Conserve toutes les lignes. Il s'agit de l'option par défaut.
`DELETE ROWS`	Supprime toutes les lignes, mais conserve la structure de la table.
`DROP`	Supprime entièrement la table.

Exemple :

CREATE TEMPORARY TABLE temp_foo (a int, b text) ON COMMIT DROP;

Définir des contraintes

Les contraintes limitent les données stockables dans une table, que ce soit au niveau de la colonne ou de la table.

Avant de définir des contraintes, tenez compte des règles suivantes :

Les contraintes CHECK ne peuvent faire référence qu'aux colonnes de la table sur laquelle elles sont définies.
Les contraintes UNIQUE et PRIMARY KEY doivent inclure la clé de distribution. Ces contraintes ne sont pas autorisées sur les tables optimisées pour l'ajout (AO) ou orientées colonnes.
Les contraintes FOREIGN KEY sont autorisées mais non appliquées.
Les contraintes définies sur une partition s'appliquent à toutes les autres partitions. Les contraintes ne peuvent pas être limitées à des partitions individuelles.

Syntaxe :

UNIQUE ( column_name [, ... ] )
  | PRIMARY KEY ( column_name [, ... ] )
  | CHECK ( expression )
  | FOREIGN KEY ( column_name [, ... ] )
           REFERENCES table_name [ ( column_name [, ... ] ) ]
           [ key_match_type ]
           [ key_action ]
           [ key_checking_mode ]

Contraintes CHECK

Une contrainte CHECK impose aux valeurs de colonne de satisfaire une expression booléenne.

CREATE TABLE products (
  product_no integer,
  name text,
  price numeric CHECK (price > 0)
);

Contraintes NOT NULL

Une contrainte NOT NULL empêche une colonne de contenir des valeurs NULL.

CREATE TABLE products (
  product_no integer NOT NULL,
  name text NOT NULL,
  price numeric
);

Contraintes UNIQUE

Une contrainte UNIQUE garantit l'unicité des valeurs d'une colonne ou d'un groupe de colonnes pour toutes les lignes. La table doit utiliser une distribution par hachage et les colonnes de contrainte doivent inclure la clé de distribution.

CREATE TABLE products (
  product_no integer UNIQUE,
  name text,
  price numeric)
  DISTRIBUTED BY (product_no);

Contraintes PRIMARY KEY

Une contrainte PRIMARY KEY combine une contrainte UNIQUE et une contrainte NOT NULL. La table doit utiliser une distribution par hachage et les colonnes de contrainte doivent inclure la clé de distribution.

Lorsqu'une table possède une clé primaire, celle-ci sert par défaut de clé de distribution.

CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name text,
  price numeric)
  DISTRIBUTED BY (product_no);