Taules i restriccions

CREATE TABLE

La instrucció CREATE TABLE crea una nova taula amb les seves columnes i restriccions d'integritat.

Sintaxi bàsica

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE TABLE clients (
    id_client   SERIAL          PRIMARY KEY,
    dni         CHAR(9)         NOT NULL UNIQUE,
    nom         VARCHAR(100)    NOT NULL,
    cognoms     VARCHAR(200)    NOT NULL,
    email       VARCHAR(150)    UNIQUE,
    telefon     VARCHAR(15),
    data_alta   DATE            NOT NULL DEFAULT CURRENT_DATE,
    actiu       BOOLEAN         NOT NULL DEFAULT TRUE
);

CREATE TABLE clients (
    id_client   INT             AUTO_INCREMENT PRIMARY KEY,
    dni         CHAR(9)         NOT NULL UNIQUE,
    nom         VARCHAR(100)    NOT NULL,
    cognoms     VARCHAR(200)    NOT NULL,
    email       VARCHAR(150)    UNIQUE,
    telefon     VARCHAR(15),
    data_alta   DATE            NOT NULL DEFAULT (CURRENT_DATE),
    actiu       TINYINT(1)      NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
    id_client   INT             IDENTITY(1,1) PRIMARY KEY,
    dni         CHAR(9)         NOT NULL UNIQUE,
    nom         NVARCHAR(100)   NOT NULL,
    cognoms     NVARCHAR(200)   NOT NULL,
    email       NVARCHAR(150)   UNIQUE,
    telefon     NVARCHAR(15),
    data_alta   DATE            NOT NULL DEFAULT GETDATE(),
    actiu       BIT             NOT NULL DEFAULT 1
);

CREATE TABLE clients (
    id_client   NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dni         CHAR(9)         NOT NULL UNIQUE,
    nom         VARCHAR2(100)   NOT NULL,
    cognoms     VARCHAR2(200)   NOT NULL,
    email       VARCHAR2(150)   UNIQUE,
    telefon     VARCHAR2(15),
    data_alta   DATE            DEFAULT SYSDATE NOT NULL,
    actiu       NUMBER(1)       DEFAULT 1 NOT NULL CHECK (actiu IN (0,1))
);

Tipus de dades a Oracle

Oracle usa VARCHAR2 (no VARCHAR), NUMBER per a tots els enters i decimals, i no té tipus BOOLEAN natiu en SQL (s'emula amb NUMBER(1) o CHAR(1)). A partir d'Oracle 23c sí que existeix BOOLEAN natiu.

---

Tipus de dades principals

Categoria	PostgreSQL	MySQL/MariaDB	SQL Server	Oracle	Descripció
Enters	SMALLINT, INTEGER, BIGINT	SMALLINT, INT, BIGINT	SMALLINT, INT, BIGINT	NUMBER(p), INTEGER	Nombres enters
Decimals	NUMERIC(p,s), REAL, DOUBLE PRECISION	DECIMAL(p,s), FLOAT, DOUBLE	DECIMAL(p,s), FLOAT, REAL	NUMBER(p,s), FLOAT(p), BINARY_FLOAT, BINARY_DOUBLE	Nombres amb decimals
Text fixe	CHAR(n)	CHAR(n)	CHAR(n), NCHAR(n)	CHAR(n), NCHAR(n)	Longitud fixa
Text variable	VARCHAR(n), TEXT	VARCHAR(n), TEXT	VARCHAR(n), NVARCHAR(n), TEXT	VARCHAR2(n), NVARCHAR2(n), CLOB	Longitud variable
Data/Hora	DATE, TIME, TIMESTAMP, TIMESTAMPTZ	DATE, TIME, DATETIME, TIMESTAMP	DATE, TIME, DATETIME, DATETIME2	DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE	Data i hora
Booleà	BOOLEAN	TINYINT(1)	BIT	NUMBER(1) / CHAR(1) (≥23c: BOOLEAN)	Valor vertader/fals
Binari gran	BYTEA	BLOB, MEDIUMBLOB	VARBINARY(MAX)	BLOB, RAW(n)	Dades binàries
Text gran	TEXT	LONGTEXT	NVARCHAR(MAX)	CLOB, NCLOB	Text de longitud il·limitada
JSON	JSON, JSONB	JSON	Funcions natives JSON	JSON (≥21c), CLOB	Documents JSON
UUID	UUID	CHAR(36)	UNIQUEIDENTIFIER	RAW(16) / VARCHAR2(36)	Identificadors únics

VARCHAR vs TEXT

A PostgreSQL i MySQL, TEXT emmagatzema text de longitud arbitrària. A PostgreSQL, no hi ha penalització de rendiment per usar TEXT en lloc de VARCHAR(n) (ambdós són equivalents en rendiment). A SQL Server, TEXT és un tipus antic; cal usar NVARCHAR(MAX).

Tipus de dades a Oracle

Oracle té un sistema de tipus propi que cal conèixer:

• NUMBER(p,s) és l'únic tipus numèric bàsic d'Oracle: cobreix tant enters com decimals. p és la precisió total (màx. 38 dígits) i s l'escala (decimals). INTEGER és un àlies de NUMBER(38).
• VARCHAR2(n) és el tipus de text variable natiu d'Oracle. Eviteu VARCHAR (reservat per a ús futur). La mida màxima és 4.000 bytes per defecte (o 32.767 bytes amb MAX_STRING_SIZE=EXTENDED).
• DATE a Oracle emmagatzema data i hora (fins a segons), a diferència dels altres motors. No existeix un tipus TIME independent.
• TIMESTAMP estén DATE amb precisió de subsegons (fins a 9 dígits fraccionaris). TIMESTAMP WITH TIME ZONE afegeix informació de fus horari.
• CLOB / NCLOB emmagatzemen text de fins a 128 TB. BLOB fa el mateix per a dades binàries.
• BOOLEAN natiu existeix des d'Oracle 23c. En versions anteriors s'emula amb NUMBER(1) (0/1) o CHAR(1) ('Y'/'N').
• RAW(n) emmagatzema dades binàries de longitud variable (fins a 2.000 bytes). Per a binaris grans cal usar BLOB.

---

Restriccions d'integritat (Constraints)

Les restriccions (constraints) són regles que el SGBD aplica automàticament per garantir la validesa de les dades. Si una operació violaria una restricció, el SGBD rebutja l'operació.

PRIMARY KEY

Identifica unívocament cada fila. Implica NOT NULL + UNIQUE. Cada taula pot tenir només una PRIMARY KEY.

Una PRIMARY KEY pot ser:

• Simple: un únic camp, habitualment un identificador numèric autoincrementat (id_producte, id_client…). És l'opció preferida quan es pot afegir un camp artificial únic.
• Composta: dos o més camps que, conjuntament, identifiquen unívocament cada fila. Cap dels camps per separat és únic; la unicitat la garanteix la combinació.

Quan usar una PK composta? En taules associatives (relacions N:M) on cada fila representa la intersecció de dues entitats. A linies_comanda, per exemple, una comanda pot tenir diverses línies i el valor num_linia = 1 existeix a moltes comandes. La combinació (id_comanda, num_linia) sí que és única i té significat de negoci: "la línia 1 de la comanda 42".

PK artificial vs PK natural

Davant el dubte, preferiu una PK artificial (SERIAL/IDENTITY/GENERATED AS IDENTITY). Les PK compostes fan que les FK que les referencien siguin més complexes i els JOINs més verbosos. Useu PK compostes quan la combinació de camps ja té significat propi i no voleu afegir redundància.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- PK simple (a nivell de columna)
CREATE TABLE productes (
    id_producte SERIAL          PRIMARY KEY,
    nom         VARCHAR(200)    NOT NULL
);

-- PK composta (a nivell de taula, obligatori quan hi ha més d'un camp)
CREATE TABLE linies_comanda (
    id_comanda  INTEGER         NOT NULL,
    num_linia   SMALLINT        NOT NULL,
    id_producte INTEGER         NOT NULL,
    quantitat   INTEGER         NOT NULL CHECK (quantitat > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia)
);

-- PK simple
CREATE TABLE productes (
    id_producte INT             AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(200)    NOT NULL
);

-- PK composta
CREATE TABLE linies_comanda (
    id_comanda  INT             NOT NULL,
    num_linia   SMALLINT        NOT NULL,
    id_producte INT             NOT NULL,
    quantitat   INT             NOT NULL CHECK (quantitat > 0),
    PRIMARY KEY (id_comanda, num_linia)
) ENGINE=InnoDB;

-- PK simple
CREATE TABLE productes (
    id_producte INT             IDENTITY(1,1) PRIMARY KEY,
    nom         NVARCHAR(200)   NOT NULL
);

-- PK composta
CREATE TABLE linies_comanda (
    id_comanda  INT             NOT NULL,
    num_linia   SMALLINT        NOT NULL,
    id_producte INT             NOT NULL,
    quantitat   INT             NOT NULL CONSTRAINT chk_qtt CHECK (quantitat > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia)
);

-- PK simple
CREATE TABLE productes (
    id_producte NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom         VARCHAR2(200)   NOT NULL
);

-- PK composta
CREATE TABLE linies_comanda (
    id_comanda  NUMBER          NOT NULL,
    num_linia   NUMBER(5)       NOT NULL,
    id_producte NUMBER          NOT NULL,
    quantitat   NUMBER          NOT NULL CONSTRAINT chk_qtt CHECK (quantitat > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia)
);

FOREIGN KEY

Estableix una relació entre taules i garanteix la integritat referencial.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE TABLE comandes (
    id_comanda  SERIAL      PRIMARY KEY,
    id_client   INTEGER     NOT NULL,
    data_com    DATE        NOT NULL DEFAULT CURRENT_DATE,
    total       numèric(10,2),

    CONSTRAINT fk_comandes_client
        FOREIGN KEY (id_client)
        REFERENCES clients(id_client)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE comandes (
    id_comanda  INT         AUTO_INCREMENT PRIMARY KEY,
    id_client   INT         NOT NULL,
    data_com    DATE        NOT NULL DEFAULT (CURRENT_DATE),
    total       DECIMAL(10,2),

    CONSTRAINT fk_comandes_client
        FOREIGN KEY (id_client)
        REFERENCES clients(id_client)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE comandes (
    id_comanda  INT         IDENTITY(1,1) PRIMARY KEY,
    id_client   INT         NOT NULL,
    data_com    DATE        NOT NULL DEFAULT GETDATE(),
    total       DECIMAL(10,2),

    CONSTRAINT fk_comandes_client
        FOREIGN KEY (id_client)
        REFERENCES clients(id_client)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

CREATE TABLE comandes (
    id_comanda  NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_client   NUMBER          NOT NULL,
    data_com    DATE            DEFAULT SYSDATE NOT NULL,
    total       NUMBER(10,2),

    CONSTRAINT fk_comandes_client
        FOREIGN KEY (id_client)
        REFERENCES clients(id_client)
        ON DELETE NO ACTION
    -- Oracle no suporta ON UPDATE CASCADE
);

Oracle i ON UPDATE CASCADE

Oracle no suporta ON UPDATE CASCADE. Les claus foranes d'Oracle accepten ON DELETE CASCADE i ON DELETE SET NULL, però no cap opció ON UPDATE. Si cal propagar canvis a la PK, cal fer-ho manualment o evitar canviar les claus primàries.

Clau forana que referencia una PK composta

Quan la taula referenciada té una PRIMARY KEY composta, la FOREIGN KEY ha d'incloure tots els camps de la PK en el mateix ordre. Els camps de la FK s'han de correspondre un a un amb els camps de la PK referenciada.

Exemple: la taula devolucions registra la devolució d'una línia concreta d'una comanda. Com que linies_comanda té la PK composta (id_comanda, num_linia), la FK ha de referenciar els dos camps alhora: no n'hi ha prou amb saber a quina comanda pertany, cal saber també quina línia concreta es retorna.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE TABLE devolucions (
    id_devolucio    SERIAL          PRIMARY KEY,
    id_comanda      INTEGER         NOT NULL,
    num_linia       SMALLINT        NOT NULL,
    motiu           VARCHAR(200),
    data_devolucio  DATE            NOT NULL DEFAULT CURRENT_DATE,

    -- FK composta: referencia els DOS camps de la PK de linies_comanda
    CONSTRAINT fk_dev_linia
        FOREIGN KEY (id_comanda, num_linia)
        REFERENCES linies_comanda(id_comanda, num_linia)
        ON DELETE RESTRICT
);

CREATE TABLE devolucions (
    id_devolucio    INT             AUTO_INCREMENT PRIMARY KEY,
    id_comanda      INT             NOT NULL,
    num_linia       SMALLINT        NOT NULL,
    motiu           VARCHAR(200),
    data_devolucio  DATE            NOT NULL DEFAULT (CURRENT_DATE),

    CONSTRAINT fk_dev_linia
        FOREIGN KEY (id_comanda, num_linia)
        REFERENCES linies_comanda(id_comanda, num_linia)
        ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE devolucions (
    id_devolucio    INT             IDENTITY(1,1) PRIMARY KEY,
    id_comanda      INT             NOT NULL,
    num_linia       SMALLINT        NOT NULL,
    motiu           NVARCHAR(200),
    data_devolucio  DATE            NOT NULL DEFAULT GETDATE(),

    CONSTRAINT fk_dev_linia
        FOREIGN KEY (id_comanda, num_linia)
        REFERENCES linies_comanda(id_comanda, num_linia)
        ON DELETE NO ACTION
);

CREATE TABLE devolucions (
    id_devolucio    NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_comanda      NUMBER          NOT NULL,
    num_linia       NUMBER(5)       NOT NULL,
    motiu           VARCHAR2(200),
    data_devolucio  DATE            DEFAULT SYSDATE NOT NULL,

    CONSTRAINT fk_dev_linia
        FOREIGN KEY (id_comanda, num_linia)
        REFERENCES linies_comanda(id_comanda, num_linia)
        ON DELETE NO ACTION
);

Índex sobre la FK composta

Molts motors no creen automàticament un índex sobre les columnes d'una FK. En FKs compostes és especialment important crear-lo manualment, ja que els JOINs i les comprovacions d'integritat referencial el necessiten:

-- PostgreSQL / MySQL / Oracle
CREATE INDEX idx_dev_linia ON devolucions(id_comanda, num_linia);

-- SQL Server
CREATE INDEX idx_dev_linia ON devolucions(id_comanda, num_linia);

UNIQUE

Garanteix que els valors d'una columna (o combinació de columnes) són únics a tota la taula. A diferència de PRIMARY KEY, una taula pot tenir múltiples restriccions UNIQUE i permeten valors NULL (en la majoria de motors, múltiples NULL es consideren no iguals).

-- En la definició de columna
email VARCHAR(150) UNIQUE,

-- Com a restricció de taula (millor per a noms explícits)
CONSTRAINT uq_clients_email UNIQUE (email),

-- UNIQUE compost
CONSTRAINT uq_pistes_franja UNIQUE (id_instal·lació, data_reserva, hora_inici)

NOT NULL

Garanteix que la columna no pot contenir valors NULL. Sense NOT NULL, el valor per defecte és NULL (accepta valors buits).

nom VARCHAR(100) NOT NULL,

NULL no és zero ni cadena buida

NULL significa "valor desconegut o absent". NULL ≠ 0, NULL ≠ '', NULL ≠ FALSE. Les comparacions amb NULL sempre retornen NULL (no TRUE ni FALSE). Per verificar si un valor és NULL cal usar IS NULL o IS NOT NULL, mai = NULL.

CHECK

Defineix una condició que cada fila ha de satisfer. El SGBD rebutja qualsevol INSERT o UPDATE que violi la condició.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE TABLE productes (
    id_producte SERIAL PRIMARY KEY,
    nom         VARCHAR(200) NOT NULL,
    preu        numèric(10,2) NOT NULL CHECK (preu > 0),
    estoc       INTEGER NOT NULL DEFAULT 0 CHECK (estoc >= 0),
    categoria   VARCHAR(50) CHECK (categoria IN ('electronics', 'roba', 'alimentacio', 'llar'))
);

-- CHECK compost (a nivell de taula)
CREATE TABLE reserves (
    id_reserva  SERIAL PRIMARY KEY,
    hora_inici  TIME NOT NULL,
    hora_fi     TIME NOT NULL,
    CONSTRAINT chk_hores CHECK (hora_fi > hora_inici)
);

-- CHECK suportat des de MySQL 8.0 / MariaDB 10.2
CREATE TABLE productes (
    id_producte INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(200) NOT NULL,
    preu        DECIMAL(10,2) NOT NULL,
    estoc       INT NOT NULL DEFAULT 0,
    CONSTRAINT chk_preu CHECK (preu > 0),
    CONSTRAINT chk_estoc CHECK (estoc >= 0)
) ENGINE=InnoDB;

CREATE TABLE productes (
    id_producte INT IDENTITY(1,1) PRIMARY KEY,
    nom         NVARCHAR(200) NOT NULL,
    preu        DECIMAL(10,2) NOT NULL CONSTRAINT chk_preu CHECK (preu > 0),
    estoc       INT NOT NULL DEFAULT 0 CONSTRAINT chk_estoc CHECK (estoc >= 0)
);

CREATE TABLE productes (
    id_producte NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom         VARCHAR2(200)   NOT NULL,
    preu        NUMBER(10,2)    NOT NULL CONSTRAINT chk_preu CHECK (preu > 0),
    estoc       NUMBER          DEFAULT 0 NOT NULL CONSTRAINT chk_estoc CHECK (estoc >= 0),
    categoria   VARCHAR2(50)    CONSTRAINT chk_cat CHECK (categoria IN ('electronics', 'roba', 'alimentacio', 'llar'))
);

-- CHECK compost (a nivell de taula)
CREATE TABLE reserves (
    id_reserva  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    hora_inici  DATE NOT NULL,
    hora_fi     DATE NOT NULL,
    CONSTRAINT chk_hores CHECK (hora_fi > hora_inici)
);

DATE a Oracle

A Oracle, DATE emmagatzema data i hora (fins a segons). No existeix un tipus TIME independent. Per a precisió de subsegons cal usar TIMESTAMP.

DEFAULT

Especifica el valor que s'assigna automàticament quan no és proporciona un valor explícit en un INSERT.

-- Valors per defecte típics
data_alta    DATE     DEFAULT CURRENT_DATE,
actiu        BOOLEAN  DEFAULT TRUE,
estat        VARCHAR  DEFAULT 'pendent',
data_creacio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
comptador    INTEGER  DEFAULT 0

---

Exemple complet: Esquema d'una botiga

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE TABLE categories (
    id_categoria SERIAL PRIMARY KEY,
    nom          VARCHAR(100) NOT NULL UNIQUE,
    descripcio   TEXT
);

CREATE TABLE productes (
    id_producte  SERIAL PRIMARY KEY,
    id_categoria INTEGER NOT NULL,
    codi_barres  VARCHAR(20) UNIQUE,
    nom          VARCHAR(200) NOT NULL,
    preu         NUMERIC(10,2) NOT NULL CHECK (preu > 0),
    estoc        INTEGER NOT NULL DEFAULT 0 CHECK (estoc >= 0),
    actiu        BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT fk_prod_cat FOREIGN KEY (id_categoria)
        REFERENCES categories(id_categoria) ON DELETE RESTRICT
);

CREATE TABLE clients (
    id_client  SERIAL PRIMARY KEY,
    dni        CHAR(9) NOT NULL UNIQUE,
    nom        VARCHAR(100) NOT NULL,
    cognoms    VARCHAR(200) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    data_alta  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE comandes (
    id_comanda SERIAL PRIMARY KEY,
    id_client  INTEGER NOT NULL,
    data_com   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    estat      VARCHAR(20) NOT NULL DEFAULT 'pendent'
               CHECK (estat IN ('pendent', 'confirmada', 'enviada', 'entregada', 'cancel·lada')),
    total      NUMERIC(10,2),
    CONSTRAINT fk_com_cli FOREIGN KEY (id_client)
        REFERENCES clients(id_client) ON DELETE RESTRICT
);

CREATE TABLE linies_comanda (
    id_comanda    INTEGER NOT NULL,
    num_linia     SMALLINT NOT NULL,
    id_producte   INTEGER NOT NULL,
    quantitat     INTEGER NOT NULL CHECK (quantitat > 0),
    preu_unitari  NUMERIC(10,2) NOT NULL CHECK (preu_unitari > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia),
    CONSTRAINT fk_lin_com FOREIGN KEY (id_comanda)
        REFERENCES comandes(id_comanda) ON DELETE CASCADE,
    CONSTRAINT fk_lin_pro FOREIGN KEY (id_producte)
        REFERENCES productes(id_producte) ON DELETE RESTRICT
);

CREATE TABLE categories (
    id_categoria INT             AUTO_INCREMENT PRIMARY KEY,
    nom          VARCHAR(100)    NOT NULL UNIQUE,
    descripcio   TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE productes (
    id_producte  INT             AUTO_INCREMENT PRIMARY KEY,
    id_categoria INT             NOT NULL,
    codi_barres  VARCHAR(20)     UNIQUE,
    nom          VARCHAR(200)    NOT NULL,
    preu         DECIMAL(10,2)   NOT NULL CHECK (preu > 0),
    estoc        INT             NOT NULL DEFAULT 0 CHECK (estoc >= 0),
    actiu        TINYINT(1)      NOT NULL DEFAULT 1,
    CONSTRAINT fk_prod_cat FOREIGN KEY (id_categoria)
        REFERENCES categories(id_categoria) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
    id_client  INT             AUTO_INCREMENT PRIMARY KEY,
    dni        CHAR(9)         NOT NULL UNIQUE,
    nom        VARCHAR(100)    NOT NULL,
    cognoms    VARCHAR(200)    NOT NULL,
    email      VARCHAR(150)    NOT NULL UNIQUE,
    data_alta  DATE            NOT NULL DEFAULT (CURRENT_DATE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE comandes (
    id_comanda INT             AUTO_INCREMENT PRIMARY KEY,
    id_client  INT             NOT NULL,
    data_com   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    estat      VARCHAR(20)     NOT NULL DEFAULT 'pendent',
    CONSTRAINT chk_estat CHECK (estat IN ('pendent', 'confirmada', 'enviada', 'entregada', 'cancel·lada')),
    total      DECIMAL(10,2),
    CONSTRAINT fk_com_cli FOREIGN KEY (id_client)
        REFERENCES clients(id_client) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE linies_comanda (
    id_comanda    INT             NOT NULL,
    num_linia     SMALLINT        NOT NULL,
    id_producte   INT             NOT NULL,
    quantitat     INT             NOT NULL CHECK (quantitat > 0),
    preu_unitari  DECIMAL(10,2)   NOT NULL CHECK (preu_unitari > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia),
    CONSTRAINT fk_lin_com FOREIGN KEY (id_comanda)
        REFERENCES comandes(id_comanda) ON DELETE CASCADE,
    CONSTRAINT fk_lin_pro FOREIGN KEY (id_producte)
        REFERENCES productes(id_producte) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
    id_categoria INT             IDENTITY(1,1) PRIMARY KEY,
    nom          NVARCHAR(100)   NOT NULL UNIQUE,
    descripcio   NVARCHAR(MAX)
);

CREATE TABLE productes (
    id_producte  INT             IDENTITY(1,1) PRIMARY KEY,
    id_categoria INT             NOT NULL,
    codi_barres  NVARCHAR(20)    UNIQUE,
    nom          NVARCHAR(200)   NOT NULL,
    preu         DECIMAL(10,2)   NOT NULL CONSTRAINT chk_preu CHECK (preu > 0),
    estoc        INT             NOT NULL DEFAULT 0 CONSTRAINT chk_estoc CHECK (estoc >= 0),
    actiu        BIT             NOT NULL DEFAULT 1,
    CONSTRAINT fk_prod_cat FOREIGN KEY (id_categoria)
        REFERENCES categories(id_categoria) ON DELETE NO ACTION
);

CREATE TABLE clients (
    id_client  INT             IDENTITY(1,1) PRIMARY KEY,
    dni        CHAR(9)         NOT NULL UNIQUE,
    nom        NVARCHAR(100)   NOT NULL,
    cognoms    NVARCHAR(200)   NOT NULL,
    email      NVARCHAR(150)   NOT NULL UNIQUE,
    data_alta  DATE            NOT NULL DEFAULT GETDATE()
);

CREATE TABLE comandes (
    id_comanda INT             IDENTITY(1,1) PRIMARY KEY,
    id_client  INT             NOT NULL,
    data_com   DATETIME2       NOT NULL DEFAULT GETDATE(),
    estat      NVARCHAR(20)    NOT NULL DEFAULT 'pendent'
               CONSTRAINT chk_estat CHECK (estat IN ('pendent', 'confirmada', 'enviada', 'entregada', 'cancel·lada')),
    total      DECIMAL(10,2),
    CONSTRAINT fk_com_cli FOREIGN KEY (id_client)
        REFERENCES clients(id_client) ON DELETE NO ACTION
);

CREATE TABLE linies_comanda (
    id_comanda    INT             NOT NULL,
    num_linia     SMALLINT        NOT NULL,
    id_producte   INT             NOT NULL,
    quantitat     INT             NOT NULL CONSTRAINT chk_qtt CHECK (quantitat > 0),
    preu_unitari  DECIMAL(10,2)   NOT NULL CONSTRAINT chk_preu_u CHECK (preu_unitari > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia),
    CONSTRAINT fk_lin_com FOREIGN KEY (id_comanda)
        REFERENCES comandes(id_comanda) ON DELETE CASCADE,
    CONSTRAINT fk_lin_pro FOREIGN KEY (id_producte)
        REFERENCES productes(id_producte) ON DELETE NO ACTION
);

CREATE TABLE categories (
    id_categoria NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom          VARCHAR2(100)   NOT NULL UNIQUE,
    descripcio   VARCHAR2(4000)
);

CREATE TABLE productes (
    id_producte  NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_categoria NUMBER          NOT NULL,
    codi_barres  VARCHAR2(20)    UNIQUE,
    nom          VARCHAR2(200)   NOT NULL,
    preu         NUMBER(10,2)    NOT NULL CHECK (preu > 0),
    estoc        NUMBER          DEFAULT 0 NOT NULL CHECK (estoc >= 0),
    actiu        NUMBER(1)       DEFAULT 1 NOT NULL CHECK (actiu IN (0,1)),
    CONSTRAINT fk_prod_cat FOREIGN KEY (id_categoria)
        REFERENCES categories(id_categoria) ON DELETE NO ACTION
);

CREATE TABLE clients (
    id_client  NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dni        CHAR(9)         NOT NULL UNIQUE,
    nom        VARCHAR2(100)   NOT NULL,
    cognoms    VARCHAR2(200)   NOT NULL,
    email      VARCHAR2(150)   NOT NULL UNIQUE,
    data_alta  DATE            DEFAULT SYSDATE NOT NULL
);

CREATE TABLE comandes (
    id_comanda NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_client  NUMBER          NOT NULL,
    data_com   TIMESTAMP       DEFAULT CURRENT_TIMESTAMP NOT NULL,
    estat      VARCHAR2(20)    DEFAULT 'pendent' NOT NULL
               CHECK (estat IN ('pendent', 'confirmada', 'enviada', 'entregada', 'cancel·lada')),
    total      NUMBER(10,2),
    CONSTRAINT fk_com_cli FOREIGN KEY (id_client)
        REFERENCES clients(id_client) ON DELETE NO ACTION
);

CREATE TABLE linies_comanda (
    id_comanda    NUMBER      NOT NULL,
    num_linia     NUMBER(5)   NOT NULL,
    id_producte   NUMBER      NOT NULL,
    quantitat     NUMBER      NOT NULL CHECK (quantitat > 0),
    preu_unitari  NUMBER(10,2) NOT NULL CHECK (preu_unitari > 0),
    CONSTRAINT pk_linies PRIMARY KEY (id_comanda, num_linia),
    CONSTRAINT fk_lin_com FOREIGN KEY (id_comanda)
        REFERENCES comandes(id_comanda) ON DELETE CASCADE,
    CONSTRAINT fk_lin_pro FOREIGN KEY (id_producte)
        REFERENCES productes(id_producte) ON DELETE NO ACTION
);

AC0372/03/04 — Miniactivitat

RA3 · CA3.3, CA3.4

Implementeu el schema anterior en un contenidor PostgreSQL Docker. després:

1. Intenteu inserir un producte amb preu negatiu. Que passa?
2. Intenteu inserir una comanda amb un id_client inexistent. Que passa?
3. Intenteu eliminar una categoria que te productes associats. Que passa?

Documenteu els missatges d'error del SGBD i expliqueu quina restricció s'ha violat en cada cas.