Material: DDL - Llenguatge de Definició de Dades

<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # DDL - Llenguatge de Definició de Dades <div class="text-end fit-content ms-auto my-3 mt-auto pt-3"> <p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p> <p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p> <p><strong>Curs:</strong> 2023/2024</p> </div> <div> <p class="fw-bold mb-0">Llicència: BY-NC-SA</p> <p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p> <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank"> <img class="mx-auto" src="/itb/images/license.png" alt="Licence"/> </a> </div> </div> </div> {:toc} ## Introducció Una vegada hem dissenyat i modelat, de manera teòrica, l'estructura de la base de dades relacional, el que cal fer és definir aquest model en el __Sistema Gestor de Bases de Dades__. ![DBMS](/itb/DAM-BD/UD4/img/sql_dbms.png){.center} Cada SGBD té la seua manera de definir aquest model. No obstant això, en els SGBD relacionals, normalment s'utilitza __SQL__ per comunicar-nos i interactuar amb aquest. ## SQL __SQL (_Structured Query Language_)__ és un llenguatge que permet gestionar i recuperar les dades emmagatzemades en una base de dades relacional. Algunes de les característiques són: - Permet la comunicació amb el SGBD. Cada SGBD té el seu propi llenguatge. - És un llenguatge que pot ser utilitzar per diferents perfils d'usuaris: Administradors i Programadors. - És un llenguatge __declaratiu__. S'especifica el resultat que es vol obtindre, no el com. Depenent de l'objectiu de les sentències SQL, es poden classificar en els següents subllenguatges: - __Llenguatge DDL__: llenguatge de definició de dades (_Data Definition Language_). Permet crear tota l'estructura d'una base de dades, les relacions entre les dades i les regles que han de complir. Les operacions són de tipus `DROP` (esborrar), `ALTER` (modificar) i `CREATE` (crear). - __Llenguatge DML__: llenguatge de manipulació de dades (_Data Manipulation Language_). Aquest llenguatge permet: `SELECT` (seleccionar/buscar), `INSERT` (inserir), `UPDATE` (actualitzar) i `DELETE` (esborrar). - __Llenguatge DCL__: llenguatge de control de dades (_Data Control Language_). Inclou comandes com `GRANT` i `REVOKE` que permeten a l'administrador gestionar l'accés a les dades contingudes en la base de dades. - __Llenguatge TCL__: llenguatge de control de transaccions (_Transaction Control Language_). El propòsit d'aquest llenguatge és permetre executar varies comandes de forma simultània com si foren una comanda atòmica o indivisible. Si és possible, s'aplica la transacció (`COMMIT`), i si, en algun pas de l'execució passa quelcom inesperat, es poden desfer tots els passos realitzats (`ROLLBACK`). - __Llenguatge de procediments__: Permet la creació de funcions, procediments, rutines i disparadors ens bases de dades SQL. En les bases de dades SQL d'Oracle rep el nom de __PL/SQL (_Procedural Language for SQL_)__, afegeix una extensió per sobre de SQL. No existeix un estàndard. En aquest material, ens centrarem en el subllenguatge __DDL__. ## DDL El __llenguatge DDL__ significa Llenguatge de Definició de Dades (_Data Definition Language_). Aquest subllenguatge de SQL permet crear tota l'estructura d'una base de dades, les relacions entre les dades i les regles que han de complir. Les operacions són de tipus `DROP` (esborrar), `ALTER` (modificar) i `CREATE` (crear). ::: docs Documentació oficial sobre DDL: - MariaDB: https://mariadb.com/kb/en/data-definition/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html ::: ## Base de dades La majoria de SGBD permeten crear diferents __bases de dades (_database_)__ independents. En cada base de dades, es poden definir diferents taules i contindran diferents dades, independents entre elles. ### CREATE DATABASE La sentència `CREATE DATABASE` permet crear una nova base de dades. Té el següent format: ```sql CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS] db_name ``` - `CREATE`: Indica que volem crear la base de dades. Si existeix i no s'especifica cap opció addicional, aquesta sentència fallarà. - `[OR REPLACE]`: Paràmetre opcional. Indica que, en cas que existisca prèviament, la sobreescriurem (i es perdran les dades anteriors). - `{DATABASE | SCHEMA}`: Es pot utilitzar qualsevol de les dues paraules per indicar que es desitja crear una base de dades. Són sinònims en aquest context. - `[IF NOT EXISTS]`: Paràmetre opcional. Indica que sols es crea la base de dades en cas que no existisca prèviament. - `db_name`: Nom de la base de dades. ::: docs Documentació oficial sobre crear bases de dades: - MariaDB: https://mariadb.com/kb/en/create-database/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-database.html ::: ::: example Per crear una base de dades anomenada __tenda__ cal executar la sentència: ```sql CREATE DATABASE tenda; ``` ::: ### DROP DATABASE La sentència `DROP DATABASE` permet eliminar una base de dades existent. Té el següent format: ```sql DROP {DATABASE | SCHEMA} [IF EXISTS] db_name ``` - `DROP`: Indica que volem esborrar la base de dades. Si no existeix i no s'indica cap opció addicional, la sentència fallarà. - `{DATABASE | SCHEMA}`: Es pot utilitzar qualsevol de les dues paraules per indicar que es desitja crear una base de dades. Són sinònims en aquest context. - `[IF EXISTS]`: Paràmetre opcional. Indica que sols s'esborrarà la base de dades en cas d'existir. - `db_name`: Nom de la base de dades. ::: docs Documentació oficial sobre eliminar bases de dades: - MariaDB: https://mariadb.com/kb/en/drop-database/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/drop-database.html ::: ::: example Per eliminar una base de dades anomenada __tenda__ cal executar la sentència: ```sql DROP DATABASE tenda; ``` ::: ### SHOW DATABASES La sentència `SHOW DATABASES` permet mostrar una llista amb les bases de dades existents. Té el següent format: ```sql SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr] ``` - `SHOW`: Indica que volem mostrar la llista de bases de dades. - `{DATABASES | SCHEMAS}`: Es pot utilitzar qualsevol de les dues paraules per indicar que es desitja crear una base de dades. Són sinònims en aquest context. - `[LIKE 'pattern']`: Paràmetre opcional. Permet filtrar les bases de dades mostrades a partir d'una condició. ::: info Més informació sobre `LIKE`: - MariaDB: https://mariadb.com/kb/en/like/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html S'estudiarà en la __UD5: Data Manipulation Language__. ::: ::: docs Documentació oficial sobre llistar bases de dades: - MariaDB: https://mariadb.com/kb/en/show-databases/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/show-databases.html ::: ::: example Per mostrar totes les bases de dades que comencen per la lletra 't' cal executar la comanda: ```sql SHOW DATABASES LIKE 't%'; +---------------+ | Database (t%) | +---------------+ | tenda | +---------------+ ``` ::: ### USE La sentència `USE` s'utilitza per a seleccionar una base de dades i indicar que totes les següents sentències s'executaran en una base de dades en concret. Té el següent format: ```sql USE [DATABASE] db_name; ``` - `USE`: Indica que volem utilitzar una base de dades per a les següents sentències. - `[DATABASE]`: Paràmetre opcional. No té cap efecte. Disponible a partir de la versió 11.3. - `db_name`: Nom de la base de dades. ::: docs Documentació oficial sobre seleccionar bases de dades: - MariaDB: https://mariadb.com/kb/en/use-database/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/creating-database.html ::: ::: example Per indicar que volem utilitzar la base de dades `tenda` en les següents consultes cal utilitzar: ```sql USE tenda; Database changed ``` ::: ## Taules Cada base de dades conté __taules relacionals__ que emmagatzemen les dades i les seues relacions. ### CREATE TABLE La sentència `CREATE TABLE` s'utilitza crear una taula en la base de dades. Té el següent format: ```sql CREATE [OR REPLACE] TABLE [IF NOT EXISTS] table_name ( field1_name FIELD1_TYPE [OPTIONS] [COSTRAINTS], field2_name FIELD2_TYPE [OPTIONS] [COSTRAINTS], ... ); ``` - `CREATE TABLE`: Indica que volem crear una taula. Si la taula ja existeix i no s'especifica cap de les opcions `[OR REPLACE]` o `[IF NOT EXISTS]`, la sentència fallarà. - `[OR REPLACE]`: Paràmetre opcional. Indica que sobreescriurem la taula si ja existeix anteriorment. - `[IF NOT EXISTS]`: Paràmetre opcional. Indica que es crearà la taula sols si no existeix anteriorment. - `table_name`: Paràmetre opcional. Indica que es crearà la taula sols si no existeix anteriorment. Dins de la taula es poden definir múltiples atributs, que es defineixen de la següent manera: ```sql field_name FIELD_TYPE [OPTIONS] [CONSTRAINTS] ``` - `field_name`: Nom de l'atribut. Ha de ser únic en la taula. - `FIELD_TYPE`: Tipus de l'atribut. Aquest paràmetre defineix el tipus de dades que s'emmagatzemarà en aquesta columna i el seu __domini__. - `[OPTIONS]`: Paràmetres opcionals per definir diferents opcions de la columna. S'expliquen més endavant. - `[CONSTRAINTS]`: Paràmetres opcional. Defineix les restriccions de la columna. S'expliquen més endavant. ::: docs Documentació oficial sobre crear taules: - MariaDB: https://mariadb.com/kb/en/create-table/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-table.html ::: ::: example Aquesta sentència crearà les taules `cistella_compra` i `producte` en la base de dades `tenda`. ```sql USE tenda; CREATE TABLE producte ( id_producte INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, preu DECIMAL(5, 2) NOT NULL ); CREATE TABLE cistella_compra ( id_cistella INT PRIMARY KEY AUTO_INCREMENT ); ``` ::: ### SHOW COLUMNS La sentència `SHOW COLUMNS` permet consultar les columnes d'una taula. Té el següent format: ```sql SHOW [FULL] {COLUMNS | FIELDS} {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr] ``` - `[FULL]`: Paràmetre opcional. Mostra més informació. Si la taula no existeix i no s'especifica l'opció `[IF EXISTS]`, la sentència fallarà. - `{COLUMNS | FIELDS}`: Es pot utilitzar qualesvol de les dues paraules. - `{FROM | IN} tbl_name`: Es pot utilitzar qualesvol de les dues paraules. Indica la taula de la qual es volen consultar les columnes. - `[{FROM | IN} db_name]`: Paràmetre opcional. Es pot utilitzar qualesvol de les dues paraules. Indica en quina base de dades està la taula que es vol consultar. - `[LIKE | WHERE]`: Permet filtrar els resultats de la consulta a partir d'una condició. ::: docs Documentació oficial sobre consultar columnes: - MariaDB: https://mariadb.com/kb/en/show-columns/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/show-columns.html ::: ### DROP TABLE La sentència `DROP TABLE` s'utilitza per eliminar una taula en la base de dades. Té el següent format: ```sql DROP TABLE [IF EXISTS] table_name [, table2_name ...]; ``` - `DROP TABLE`: Indica que es vol eliminar una taula. Si la taula no existeix i no s'especifica l'opció `[IF EXISTS]`, la sentència fallarà. - `[IF EXISTS]`: Paràmetre opcional. Indica que s'eliminarà la taula si existeix. - `table_name [, table2_name ...]`: Nom de la taula que s'eliminarà. Es poden especificar vàries taules separades per comes. ::: docs Documentació oficial sobre eliminar taules: - MariaDB: https://mariadb.com/kb/en/drop-table/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/drop-table.html ::: ::: example Aquesta sentència eliminarà les taules `cistella_compra` i `producte` de la base de dades `tenda`. ```sql USE tenda; DROP TABLE cistella_compra, producte; ``` ::: ### SHOW TABLES La sentència `SHOW TABLES` s'utilitza per consultar les taules existents en una base de dades. Té el següent format: ```sql SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] ``` - `SHOW TABLES`: Indica que es consultaran les taules d'una base de dades. Si la taula no existeix i no s'especifica l'opció `[IF EXISTS]`, la sentència fallarà. - `[FULL]`: Paràmetre opcional. A més, es mostra el tipus de taula. `BASE_TABLE` per a una taula relacional, `VIEW` per a una vista i `SEQUENCE` per a una seqüència. - `[FROM db_name]`: Paràmetre opcional. Permet especificar la base de dades sobre la qual es realitza la consulta. - `[LIKE | WHERE]`: Permet filtrar els resultats de la consulta a partir d'una condició. ::: docs Documentació oficial sobre mostrar taules: - MariaDB: https://mariadb.com/kb/en/show-tables/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/show-tables.html ::: ::: example Aquesta sentència mostra les bases de dades de la base de dades actual. ```sql SHOW TABLES; +-----------------+ | Tables_in_tenda | +-----------------+ | cistella_compra | | producte | +-----------------+ ``` ::: ### ALTER TABLE La sentència `ALTER TABLE` s'utilitza per modificar la definició d'una taula existent, on es poden afegir, eliminar o modificar atributs i restriccions. ::: docs Documentació oficial sobre modificar taules: - MariaDB: https://mariadb.com/kb/en/alter-table/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html ::: #### ADD El modificador `ADD` permet afegir una columna o restricció a la taula. Té el format: ```sql ALTER TABLE table_name ADD column_name datatype [OPTIONS]; ``` ::: example Aquesta sentència afegeix l'atribut `nom_curt` a `producte`. ```sql ALTER TABLE producte ADD nom_curt VARCHAR(20); ``` ::: #### DROP El modificador `DROP` permet eliminar una columna o restricció a la taula. Té el format: ```sql ALTER TABLE table_name DROP column_name; ``` ::: example Aquesta sentència elimina l'atribut `nom_curt` de `producte`. ```sql ALTER TABLE producte DROP nom_curt; ``` ::: #### MODIFY El modificador `MODIFY` permet modificar una columna o restricció existent de la taula. Aquesta opció és útil si no es volen perdre les dades d'una columna al modificar-la. Té el format: ```sql ALTER TABLE table_name MODIFY column_name datatype [OPTIONS]; ``` ::: example Aquesta sentència canvia el tipus de l'atribut `nom_curt` de la taula `producte`. ```sql ALTER TABLE producte MODIFY nom_curt varchar(25); ``` ::: ## Tipus de dades La naturalesa de les dades que es poden emmagatzemar en les bases de dades és molt diversa i que cal tindre en compte per a realitzar un bon disseny d'aquesta. Cada atribut d'una taula pot emmagatzemar un tipus de dades diferent, que cal especificar. Cal tindre en compte com s'emmagatzemaran les dades, l'espai que ocuparan i el domini per triar un tipus de dades adequat. ::: docs Documentació oficial sobre tipus de dades: - MariaDB: https://mariadb.com/kb/en/data-types/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/data-types.html ::: ::: warning En aquest material no s'especifiquen tots els tipus de dades existents, sols els més comuns. Cal consultar la documentació per a trobar la llista completa de tipus de dades. ::: ### Numèriques Els tipus de dades numèriques poden representar informació numèrica. ::: docs Documentació oficial sobre tipus de dades numèriques: - MariaDB: https://mariadb.com/kb/en/numeric-data-type-overview/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html ::: Per emmagatzemar nombres enters tenim moltes possibilitats, depenent de com de gran (o menut) siguen els nombres a emmagatzemar. De més menut a més gran: - `TINYINT` o `INT1`: Nombre enter emmagatzemat en un _byte_. El domini és [-128, 127] amb signe i [0, 255] sense signe. - `BOOLEAN`: Sinònim de `TINYINT(1)`. S'utilitza per emmagatzemar `TRUE` o `FALSE`. Realment pot emmagatzemar nombres enters en el domini de `TINYINT`, però en la pràctica es considera que el número 0 representa `FALSE` i qualsevol altre número representa `TRUE`. - `SMALLINT` o `INT2`: Nombre enter emmagatzemat en dos _bytes_. El domini és [-32768, 32767] amb signe i [0, 65535] sense signe. - `MEDIUMINT` o `INT3`: Nombre enter emmagatzemat en tres _bytes_. El domini és [-8388608, 8388607] amb signe i [0, 16777215] sense signe. - `INT` o `INT4`: Nombre enter emmagatzemat en quatre _bytes_. El domini és [-2147483648, 2147483647] amb signe i [0, 4294967295] sense signe. - `BIGNINT` o `INT8`: Nombre enter emmagatzemat en huit _bytes_. El domini és [-9223372036854775808, 9223372036854775807] amb signe i [0, 18446744073709551615] sense signe. Per emmagatzemar nombres reals o amb decimals, tenim les següents possibilitats: - `FLOAT`: Nombre amb coma flotant emmagatzemat en quatre _bytes_. Permet emmagatzemar nombres decimals amb una precisió simple. Aquest tipus de dades és presís fins a 7 nombres decimals. - `DOUBLE` o `REAL`: Nombre amb coma flotant de doble precisió, emmagatzemat en huit _bytes_. Permet emmagatzemar nombres decimals amb una precisió "normal". Aquest tipus de dades és precís fins a 15 nombres decimals. - `DECIMAL[(M[,D]]`: Nombre real amb un nombre fix de dígits i decimals. `M` és el nombre total de dígits (per defecte 10) i `D` el nombre de decimals (per defecte 0). Els dos paràmetres són opcionals. La majoria de dades numèriques permeten les opcions `SIGNED`, `UNSIGNED` i `ZEROFILL`. - `SIGNED`: Opció per defecte si no s'especifica. Indica que es guardaran dades amb el signe. - `UNSIGNED`: Indica que es guardaran dades amb el sense signe. - `ZEROFILL`: Indica que es guardaran dades amb el sense signe i que s'omplirà l'espai sobrant amb zeros a l'esquerra. ::: warning A partir de la versió `8.0.17` de __MySQL__, les següents opcions es consideren obsoletes: - `ZEROFILL`: Considereu utilitzar altres funcionas com `LPAD()` per obtindre la mateixa funcionalitat. - `UNSIGNED` per als atributs de tipus `FLOAT`, `DOUBLE` i `DECIMAL`: Considereu utilitzar una restricció `CHECK`. ::: ### Cadena de caràcters Les cadenes de caràcters és un tipus de dades que permet representar informació alfabeticonumèrica, o el que és el mateix, dades compostes de lletres, símbols i nombres. ::: docs Documentació oficial sobre tipus de dades de cadena de caràcters: - MariaDB: https://mariadb.com/kb/en/string-data-types/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/string-types.html ::: Depenent de la longitud i format de la cadena, podem trobar les següents possibilitats: - `CHAR[(M)]`: Permet emmagatzemar una cadena de caràcters de longitud fixa `M`. `M` pot prendre els valors [0, 255]. Si no s'especifica, la `M` és 1. Si les dades són més menudes que la longitud, s'omplirà amb espais per l'esquerra. - `VARCHAR(M)`: Permet emmagatzemar una cadena de caràcters de longitud variable, fins a `M`. `M` pot prendre els valors [0, 65.532]. - `TEXT`: Permet emmagatzemar una cadena de caràcters de longitud variable fins a 65.535 caràcters. - `MEDIUMTEXT`, `LONGTEXT`: Variants que permeten emmagatzemar més caràcters. - `ENUM`: Permet emmagatzemar un valor entre uns valors predefinits. ::: example La columna `dia_setmana` sols pot prendre els valors especificats, que formarien el __domini__ d'aquesta columna. ```sql dia_setmana ENUM('dilluns', 'dimarts', 'dimecres', 'dijous', 'divendres', 'dissable', 'diumenge') ``` ::: ### Data i hora Les bases de dades també estan preparades per a gestionar dades relacionades amb les dates i les hores d'una manera eficient. ::: docs Documentació oficial sobre tipus de dades de data i hora: - MariaDB: https://mariadb.com/kb/en/date-and-time-data-types/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html ::: Tipus de dades: - `DATE`: Emmagatzema una data en el format `YYYY-MM-DD`. - `TIME`: Emmagatzema una hora en el format `HH:MM:SS.ffffff`. - `DATETIME` o `TIMESTAMP`: Emmagatzema una data i hora en el format `YYYY-MM-DD HH:MM:SS.ffffff`. - `YEAR`: Emmagatzema un any en format de 4 dígits. ### Exemple ::: example ```sql CREATE TABLE persona ( dni char(9) PRIMARY KEY, nom VARCHAR(50) NOT NULL, cognoms varchar(100) NOT NULL, data_naixement DATE, alçada INT UNSIGNED, pes DECIMAL(5, 2) CHECK (pes >= 0) ); ``` Aquesta sentència crea la taula `persona` amb les columnes: - `dni`: Cadena de caràcter de longitud fixa 9. Clau primària. - `nom`: Cadena de caràcter de longitud variable fins a 50. No pot prendre valors `NULL`. - `cognoms`: Cadena de caràcter de longitud variable fins a 100. No pot prendre valors `NULL`. - `data_naixement`: Data. - `alçada`: Nombre enter positiu (sense signe). - `pes`: Nombre decimal positiu amb 2 decimals (`nnn,nn`). ::: ## Definició de columna En cada definició d'una columna es poden especificar diferents opcions. ```sql field_name FIELD_TYPE [OPTIONS] [CONSTRAINTS] ``` ### Claus #### Clau primària En cada taula es pot definir una sola __clau primària__ mitjançant les paraules clau `PRIMARY KEY`. Les claus primàries tenen les restriccions `UNIQUE` i `NOT NULL`, és a dir, no poden haver dues entrades amb el mateix valor i no poden haver valors `NULL`. Una taula sols pot tindre una `PRIMARY KEY`. Aquesta opció es pot especificar al costat d'un atribut si i sols si aquest atribut és la __clau primària__. Si la clau primària és la combinació de múltiples atributs, cal especificar-ho per separat. ```sql field1_name FIELD1_TYPE PRIMARY KEY ..., PRIMARY KEY(field1_name, field2_name, ...) ``` ::: docs Documentació oficial sobre les claus primàries: - MariaDB: - https://mariadb.com/kb/en/create-table/#primary-key-column-option - https://mariadb.com/kb/en/getting-started-with-indexes/#primary-key - MySQL 8.0 (Veuré secció `PRIMARY KEY`): https://dev.mysql.com/doc/refman/8.0/en/create-table.html - w3schools: https://www.w3schools.com/mysql/mysql_primarykey.asp ::: ::: example ```sql USE tenda; CREATE TABLE producte ( id_producte INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, preu DECIMAL(5, 2) NOT NULL ); CREATE TABLE cistella_compra ( id_cistella INT AUTO_INCREMENT, PRIMARY KEY(id_cistella) ); CREATE TABLE cistella_producte ( id_cistella INT, id_producte INT, PRIMARY KEY(id_cistella, id_producte), ... ); ``` - La clau primària de la taula `producte` s'ha definit en l'atribut `id_producte`. - La clau primària de la taula `cistella_compra` s'ha definit en l'atribut `id_cistella`. S'ha definit per separat. - La clau primària de la taula `cistella_producte` s'ha definit en el conjunt d'atributs `(id_cistella, id_producte)`. S'ha definit per separat. ::: #### Clau única La restricció `UNIQUE` s'utilitza per especificar que un atribut, o conjunt d'atributs, no pot contindre valors repetits. Es poden definir múltiples restriccions `UNIQUE` en una taula. És opcional utilitzar la paraula `KEY`. Les claus alternatives haurien de ser definides com a `UNIQUE`. De la mateixa manera que passa amb les claus primàries, es pot especificar en la definició d'un atribut si la restricció sols l'afecta aquest. En canvi, si afecta un conjunt d'atributs, s'ha d'especificar per separat. ```sql field1_name FIELD1_TYPE UNIQUE [KEY] ..., UNIQUE [KEY](field1_name, field2_name, ...) ``` ::: docs Documentació oficial sobre les claus úniques: - MariaDB: - https://mariadb.com/kb/en/create-table/#unique-key-column-option - https://mariadb.com/kb/en/getting-started-with-indexes/#unique-index - MySQL 8.0 (Veuré secció `UNIQUE`): https://dev.mysql.com/doc/refman/8.0/en/create-table.html - w3schools: https://www.w3schools.com/sql/sql_unique.asp ::: ::: example "Clau alternativa" L'atribut `n_bastidor`, que és una clau alternativa de la taula `cotxe`, no pot contindre valors repetits i cal ser definida com a `UNIQUE`. ```sql CREATE TABLE cotxe ( matricula CHAR(7) PRIMARY KEY, n_bastidor CHAR(8) UNIQUE NOT NULL, ... ); ``` ::: ::: example "Restricció de cardinalitat" La taula `permis_conduccio` està associada a la taula `persona` en una cardinalitat `(0 ,1) <=> (1, 1)`, és a dir, un permís de conducció està associat a una persona, però una persona pot no tindre un permís de conducció. Aquesta relació s'ha modelat mitjançant una propagació de clau forana a la taula `permis_conduccio`. Per evitar que una persona puga tindre associats múltiples permisos de conducció, cal utilitzar la restricció `UNIQUE`. ```sql CREATE TABLE permis_conduccio ( id_permis INT PRIMARY KEY AUTO_INCREMENT, data_obtencio DATE NOT NULL, dni_conductor char(9) NOT NULL, FOREIGN KEY (dni_conductor) REFERENCES persona (dni), UNIQUE(dni_conductor) ); ``` ::: ::: example "Restricció de cardinalitat en relacions ternàries" La restricció `UNIQUE` també s'utilitzar per restringir la cardinalitat en relacions ternàries. La taula `ABC` representa una relació ternària entre les taules `A`, `B` i `C` La cardinalitat és la següent: - Per a un element de `A` i un de `B`, poden haver N elements de `C` associats. - Per a un element de `A` i un de `C` pot haver un element de `B` associats: `UNIQUE(A, C)` - Per a un element de `B` i un de `C` pot haver un element de `A` associats: `UNIQUE(B, C)` ```sql CREATE TABLE ABC ( id_A INT, id_B INT, id_C INT, FOREIGN KEY (id_A) REFERENCES A (id), FOREIGN KEY (id_B) REFERENCES B (id), FOREIGN KEY (id_C) REFERENCES C (id), PRIMARY KEY(id_A, id_B, id_C), UNIQUE(id_A, id_C), UNIQUE(id_B, id_C) ); ``` ::: question És la clau primària d'aquesta taula el mínim subconjunt d'atributs per identificar a cada fila? La resposta és __NO__. Com que els conjunts `(A, C)` i `(B, C)` no contenen valors repetits, són claus candidates de la taula. En aquest cas, podem definir una de les dues com a clau primària, i l'altra seria una clau alternativa. ::: #### Clau forana Les __claus foranes__ s'utilitzen en el Model Relacional per relacionar unes taules amb altres. La majoria de SGBD SQL utilitzen les paraules clau `FOREIGN KEY` per indicar que un atribut fa referència a un altre atribut d'una taula. El format és: ```sql FOREIGN KEY (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT] [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT] ``` - `FOREIGN KEY`: Paraules clau per indicar la definició d'una clau forana. - `(index_col_name, ...)`: Atribut o conjunt d'atributs de la taula que formen la clau forana. - `REFERENCES tbl_name (index_col_name,...)`: Indica que la clau forana fa referència als atributs `(index_col_name,...)` de la taula `tbl_name`. - Els atributs referenciats han de ser una __clau primària__. - El tipus dels atributs associants han de ser el mateix. - Els noms dels atributs de cada taula poden ser diferents - `ON {DELETE | UPDATE}`: Indica el comportament de la taula quan s'esborra o s'edita l'element al qual es fa referència en la clau forana. - `RESTRICT`: No deixa eliminar o modificar l'element. Valor per defecte. - `CASCADE`: Elimina també les entrades referenciades. Si un element de la taula pare és eliminat o modificat, l'eliminació o modificació es propagara a tots els elements de la taula filla que el referencien. - `SET NULL`: El valor de la clau forana passa a ser `NULL`. - `NO ACTION`: Sinònim de `RESTRICT`. - `SET DEFAULT`: El valor de la clau forana passa a ser el valor per defecte especificat en la definició de la columna. ::: docs Documentació oficial sobre les claus foranes: - MariaDB: - https://mariadb.com/kb/en/foreign-keys/ - https://mariadb.com/kb/en/create-table/#foreign-key - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html ::: ::: example ```sql USE tenda; CREATE TABLE producte ( id_producte INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, preu DECIMAL(5, 2) NOT NULL ); CREATE TABLE cistella_compra ( id_cistella INT AUTO_INCREMENT, PRIMARY KEY(id_cistella) ); CREATE TABLE cistella_producte ( id_cistella INT, id_producte INT, PRIMARY KEY(id_cistella, id_producte), FOREIGN KEY(id_cistella) REFERENCES cistella_compra (id_cistella) ON DELETE CASCADE, FOREIGN KEY(id_producte) REFERENCES producte (id_producte) ); ``` - La clau primària de la taula `producte` s'ha definit en l'atribut `id_producte`. - La clau primària de la taula `cistella_compra` s'ha definit en l'atribut `id_cistella`. S'ha definit per separat. Si s'elimina un registre de la taula `cistella`, s'eliminaran els registres de la taula `cistella_producte` associats. - La clau primària de la taula `cistella_producte` s'ha definit en el conjunt d'atributs `(id_cistella, id_producte)`. S'ha definit per separat. ::: ### Altres #### Valors Nuls Mitjançant l'opció `NULL` o `NOT NULL` es pot especificar si un atribut permet o no valors nuls. Si no s'especifica, per defecte accepta valors `NULL`. ::: example ```sql CREATE TABLE cotxe ( matricula CHAR(7) PRIMARY KEY, n_bastidor CHAR(8) UNIQUE NOT NULL, marca VARCHAR(50) NOT NULL, model VARCHAR(50) NOT NULL, km INT UNSIGNED NOT NULL DEFAULT 0, dni_propietari CHAR(9), data_compra DATE NULL, FOREIGN KEY (dni_propietari) REFERENCES persona (dni) ); ``` - Els atributs `dni_propietari` i `data_compra` de la taula `cotxe` permeten valors nuls. - La resta d'atributs no permeten valors nuls. ::: #### Valors per defecte Es pot assignar un valor per defecte a una columna mitjançant l'opció `DEFAULT`. A l'inserir un registre, aquest camp prendrà el valor per defecte si no ha sigut especificat en la consulta d'inserció. Si la columna no està definida com a `NOT NULL`, `AUTO_INCREMENT` o `TIMESTAMP`, per defecte s'assignarà el valor nul quan no s'especifique: `DEFAULT NULL`. ::: docs Documentació oficial sobre valors per defecte: - MariaDB: https://mariadb.com/kb/en/create-table/#default-column-option - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html ::: ::: example ```sql CREATE TABLE cotxe ( matricula CHAR(7) PRIMARY KEY, n_bastidor CHAR(8) UNIQUE NOT NULL, marca VARCHAR(50) NOT NULL, model VARCHAR(50) NOT NULL, km INT UNSIGNED NOT NULL DEFAULT 0, dni_propietari CHAR(9), data_compra DATE NULL, FOREIGN KEY (dni_propietari) REFERENCES persona (dni) ); INSERT INTO cotxe (matricula, n_bastidor, marca, model) values ("4231KJL", "16483920", "Ford", "Focus"); SELECT * FROM cotxe; +-----------+------------+-------+-------+----+----------------+-------------+ | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra | +-----------+------------+-------+-------+----+----------------+-------------+ | 4231KJL | 16483920 | Ford | Focus | 0 | NULL | NULL | +-----------+------------+-------+-------+----+----------------+-------------+ ``` En la consulta anterior, s'ha inserit un cotxe en el qual no s'ha especificat el propietari ni la data de compra. - El valor de `km` és 0. - El valor de `dni_propietari` és nul. - El valor de `data_compra` és nul. ::: #### AUTO_INCREMENT L'opció `AUTO_INCREMENT` s'utilitza per a indicar que el valor d'una columna pot ser directament assignat amb un comptador. - Aquesta opció sols està disponible per als nombres enters. - L'opció sols pot ser utilitzada en una clau primària. - Sols pot haver un atribut amb l'opció `AUTO_INCREMENT` en la taula. El valor d'aquest camp s'assignarà en ordre, encara que pot ser especificat en la consulta d'inserció. ::: docs Documentació oficial sobre `AUTO_INCREMENT`: - MariaDB: https://mariadb.com/kb/en/create-table/#auto_increment-column-option - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html ::: ::: example ```sql CREATE TABLE producte ( id_producte INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, preu DECIMAL(5, 2) NOT NULL ); INSERT INTO producte (nom, preu) values ("Barra de pa", "0.5"), ("Botella d'aigua", "1"); INSERT INTO producte (id_producte, nom, preu) values (10, "Napolitana xocolate", "1.5"); SELECT * FROM producte; +-------------+---------------------+------+ | id_producte | nom | preu | +-------------+---------------------+------+ | 1 | Barra de pa | 0.50 | | 2 | Botella d'aigua | 1.00 | | 10 | Napolitana xocolate | 1.50 | +-------------+---------------------+------+ ``` S'han realitzat tres insercions a la taula `producte`: - La ID del producte __Barra de pa__ ha obtingut el valor del comptador: 1 - La ID del producte __Botella d'aigua__ ha obtingut el valor del comptador: 2 - La ID del producte __Napolitana xocolate__ ha obtingut el valor de la consulta: 10 ::: #### COMMENT L'opció `COMMENT` s'utilitza per a assignar un comentari a una columna. Els comentaris es poden consultar mitjançant la consulta: ```sql SHOW FULL COLUMNS; ``` ::: docs Documentació oficial sobre els comentaris: - MariaDB: https://mariadb.com/kb/en/create-table/#comment-column-option - MySQL 8.0 (Veuré secció `COMMENT`): https://dev.mysql.com/doc/refman/8.0/en/create-table.html ::: ### Restriccions Les bases de dades permeten la implementació de restriccions en les columnes de les taules, que poden ser establides en el moment de la creació de la taula (`CREATE TABLE`) o a posteriori (`ALTER TABLE`). ::: docs Documentació oficial sobre les restriccions: - MariaDB: https://mariadb.com/kb/en/constraint/ - MySQL 8.0: - https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html - https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html ::: Es consideren com a restriccions: - `PRIMARY KEY`: Clau primària. No es permeten valors repetits ni valors nuls. - `FOREIGN KEY`: Clau forana. Referència la clau primària d'una altra taula. El valor ha d'existir en la taula referenciada i ha de ser del mateix tipus. - `UNIQUE`: No es permeten valors repetits. - `CHECK`: Permet decidir si una dada és vàlida o no respecte d'una condició establida. Totes les restriccions (excepte `FOREIGN KEY`) poden ser especificades en la mateixa definició de l'atribut, sempre que sols afecten eixe atribut concret. No obstant això, les restriccions també poden ser assignades al final de la creació de la taula i, en aquest cas, poden afectar a més d'un atribut. El format és: ```sql [CONSTRAINT [constraint_name]] constraint_expression constraint_expression: | PRIMARY KEY (index_col_name, ...) ... | FOREIGN KEY (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE reference_option] [ON UPDATE reference_option] | UNIQUE [INDEX|KEY] (index_col_name, ...) | CHECK (check_constraints) ``` Les restriccions poden ser identificades per un nom mitjançant `CONSTRAINT <name>` al pricipi de l'expressió. #### CHECK Es poden definir restriccions concretes a un atribut mitjançant l'opció `CHECK`. Aquesta opció avaluarà la condició especificada per cada valor associat: - En cas vertader (`TRUE`), deixarà inserir el valor. - En cas fals (`FALSE`), no deixarà inserir el valor. ::: example S'ha afegit la columna `descompte` a la taula `producte`. Volem evitar que el descompte puga ser major de 100%, per tant, definim una restriccuó `CHECK`. ```sql CREATE TABLE producte ( id_producte INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, preu DECIMAL(5, 2) NOT NULL, descompte DECIMAL(5, 2) UNSIGNED NOT NULL DEFAULT 0 CHECK (descompte <= 100) ); INSERT INTO producte (nom, preu, descompte) values ("Barra de pa", "0.5", 20); INSERT INTO producte (nom, preu, descompte) values ("Botella d'aigua", "1", 120); Error Code: 3819. Check constraint 'producte_chk_1' is violated. ``` :::