DDL - Creació de Taules i Restriccions

`ALTER TABLE`

La sentència ALTER TABLE s'utilitza bàsicament per a:

afegir, modificar i eliminar columnes,
afegir i eliminar restriccions,
afegir i eliminar índexs,
definir valors DEFAULT per a columnes,

A l'afegir o modificar columnes, MariaDB ens permet indicar en quina posició volem que estiguin (DESCRIBE).

Quan afegim noves columnes, les files ja existents rebran inicialment el valor DEFAULT o NULL.

Afegim una columna

Per afegir una columna o més utilitzarem la següent sintaxis:

Afegim una columna a una taula existent

ALTER TABLE <taula>
    ADD [COLUMN] [IF NOT EXISTS]
        <columna_i> <tipus> ...
        [FIRST | AFTER <columna> ];

Afegim vàries columnes a una taula existent

ALTER TABLE <taula>
    ADD [COLUMN] [IF NOT EXISTS] (
        <columna_1> <tipus> ...,
        <columna_2> <tipus> ...,
    );

Exemple

ALTER TABLE PAISOS ADD COLUMN (
    ELIMINADA BOOLEAN NOT NULL DEFAULT FALSE,
    MODIFICADA DATETIME NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE PAISOS ADD COLUMN
    CREADA DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    AFTER ELIMINADA;

Modifiquem una columna

Podem modificar una columna canviant-ne el tipus o el tamany. No sempre serà possible la modificació sobretot si ja hi tenim dades:

Modifiquem els paràmetres d'una columna

ALTER TABLE <taula>
    ALTER COLUMN <columna_i>
          SET DEFAULT [ literal | <expr> ];

Modifiquem els paràmetres d'una columna

ALTER TABLE <taula>
    ALTER COLUMN <columna_i>
          DROP DEFAULT;

Modifiquem els paràmetres i el nom d'una columna

ALTER TABLE <taula>
    CHANGE [COLUMN] [IF EXISTS] <nom_col> < nou_nom_col>
           <definicio_columna> [FIRST|AFTER <nom_col2>];

Modifiquem els paràmetres i la posició d'una columna

ALTER TABLE <taula>
    MODIFY [COLUMN] [IF EXISTS] <nom_col>
           <definicio_columna> [FIRST|AFTER <nom_col2>];

Modifiquem el nom d'una columna

ALTER TABLE <taula>
      RENAME COLUMN <nom_col> TO < nou_nom_col>;

Exemple

ALTER TABLE ADRECES
    ALTER COLUMN MUNICIPI SET DEFAULT 'Girona';

ALTER TABLE ADRECES
    ALTER COLUMN MUNICIPI DROP DEFAULT;

ALTER TABLE ADRECES
    CHANGE COLUMN CODI_POSTAL CP
        INT(5)
        CHECK (CP BETWEEN 1000 AND 52999);

-- La següent instrucció, amb dades, donarà error
-- caldria fer-la per passes. Quines?
ALTER TABLE ADRECES
    MODIFY COLUMN CP
        CHAR(5)
        CHECK (CP BETWEEN '01000' AND '52999');

Possible solució

-- Passes per a modificar la columna
-- Canviem la columna de INT a CHAR
ALTER TABLE ADRECES
    MODIFY COLUMN CP
        CHAR(5);
-- Afegim 0s a l'esquerra al Codi Postal que ho necessitin
UPDATE ADRECES SET CP = LPAD(CP,5,'0');
-- Afegim la comprovació del Codi Postal
ALTER TABLE ADRECES
    MODIFY COLUMN CP CHAR(5)
        CHECK (CP BETWEEN '01000' AND '52999');
-- Continuem ... però només a partir de la versió 10.5.2
ALTER TABLE ADRECES
    RENAME COLUMN CP TO CODI_POSTAL;
-- Sinó
ALTER TABLE ADRECES ADD CONSTRAINT CK_ADRECES_CODI_POSTAL
    CHECK (CODI_POSTAL BETWEEN '01000' AND '52999');

Eliminem una columna

A la comanda ALTER TABLE hi ha una opció DROP COLUMN per eliminar una columna d'una taula, tingui o no tingui dades.

Sintaxis

ALTER TABLE <taula>
    DROP [COLUMN] [IF EXISTS] <nom_columna> [RESTRICT|CASCADE];

Exemple

ALTER TABLE PAISOS
    DROP COLUMN CREADA,
    DROP COLUMN MODIFICADA;
ALTER TABLE PAISOS
    DROP COLUMN ELIMINADA;

`DROP TABLE`

La sentència DROP TABLE s'utilitza per eliminar taules juntament amb les seves dades.

Si, a l’eliminar la taula, s’incompleix alguna restricció de FK d’altres taules cap a la que eliminem, no se’ns dexarà eliminar la taula.

Sintaxis

DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
     <nom_taula> [, nom_taula2] ...
     [WAIT n|NOWAIT]
     [RESTRICT | CASCADE];

Exemple amb errors

-- Aquesta donarà error.
DROP TABLE PAISOS;

Exemple sense errors

-- Per la següent no ens demanarà res.
CREATE TABLE COPY_ADRECES AS SELECT * FROM ADRECES;
SELECT * FROM COPY_ADRECES;
DROP TABLE COPY_ADRECES;

`RENAME TABLE`

La sentència RENAME TABLE s'utilitza per canviar el nom d'una o més taules.

Sintaxis

RENAME TABLE[S] [IF EXISTS] <nom_taula>
       [WAIT n | NOWAIT]
       TO <nou_nom_taula>
        [, tbl_name2 TO new_tbl_name2]

Exemple

RENAME TABLE PAISOS TO PAIS;
RENAME TABLE ADRECES TO ADRECA;

RENAME TABLES PAIS TO PAISOS,
              ADRECA TO ADRECES;

`TRUNCATE TABLE`

La sentència TRUNCATE TABLE permet buidar completament una o més taules.

És equivalent a DELETE però amb algunes diferències, entre elles que no es pot desfer amb ROLLBACK i que no es pot aplicar a taules referenciades per altres taules.

Sintaxis

TRUNCATE [TABLE] <nom_taula> [WAIT n | NOWAIT];

Exemple

-- Una que dona error (sempre)
TRUNCATE TABLE PAISOS;

-- i una que no dona error
TRUNCATE TABLE ADRECES;

Comentaris

Podem guardar comentaris tant en taules com en columnes de taules.

Tant podem posar comentaris de fins a 1024 caràcters en el moment de creació com posteriorment.

Sintaxis amb Exemples

ALTER TABLE PAISOS COMMENT 'Taula de Paisos';
ALTER TABLE PAISOS
    CHANGE nom_pais VARCHAR(50) NOT NULL COMMENT 'Nom del país';

COMPTE: Al modificar la fila cal tornar a posar tota la definició d’aquesta.