DDL - Llenguatge de Definició de Dades Joan Puigcerver Ibáñez j.puigcerveribanez@edu.gva.es Llicència: CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 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. 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). Documentació 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: 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. Documentació 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 Exemple Per crear una base de dades anomenada tenda cal executar la sentència: CREATE DATABASE tenda; DROP DATABASE La sentència DROP DATABASE permet eliminar una base de dades existent. Té el següent format: 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. Documentació 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 Exemple Per eliminar una base de dades anomenada tenda cal executar la sentència: 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: 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ó. Informació 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. Documentació 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 Exemple Per mostrar totes les bases de dades que comencen per la lletra 't' cal executar la comanda: 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: 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. Documentació 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 Exemple Per indicar que volem utilitzar la base de dades tenda en les següents consultes cal utilitzar: 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: 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: 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. Documentació 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 Exemple Aquesta sentència crearà les taules cistella_compra i producte en la base de dades tenda. 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: 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ó. Documentació 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: 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. Documentació 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 Exemple Aquesta sentència eliminarà les taules cistella_compra i producte de la base de dades tenda. 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: 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ó. Documentació 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 Exemple Aquesta sentència mostra les bases de dades de la base de dades actual. 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. Documentació 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: ALTER TABLE table_name ADD column_name datatype [OPTIONS]; Exemple Aquesta sentència afegeix l'atribut nom_curt a producte. ALTER TABLE producte ADD nom_curt VARCHAR(20); DROP El modificador DROP permet eliminar una columna o restricció a la taula. Té el format: ALTER TABLE table_name DROP column_name; Exemple Aquesta sentència elimina l'atribut nom_curt de producte. 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: ALTER TABLE table_name MODIFY column_name datatype [OPTIONS]; Exemple Aquesta sentència canvia el tipus de l'atribut nom_curt de la taula producte. 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. Documentació 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 Advertència 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. Documentació 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. Advertència 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. Documentació 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. Exemple La columna dia_setmana sols pot prendre els valors especificats, que formarien el domini d'aquesta columna. 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. Documentació 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 Exemple 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. 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. field1_name FIELD1_TYPE PRIMARY KEY ..., PRIMARY KEY(field1_name, field2_name, ...) Documentació 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 Exemple 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. field1_name FIELD1_TYPE UNIQUE [KEY] ..., UNIQUE [KEY](field1_name, field2_name, ...) Documentació 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 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. CREATE TABLE cotxe ( matricula CHAR(7) PRIMARY KEY, n_bastidor CHAR(8) UNIQUE NOT NULL, ... ); 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. 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) ); 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) 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) ); Pregunta É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: 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. Documentació 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 Exemple 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. Exemple 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. Documentació 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 Exemple 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ó. Documentació 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 Exemple 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: SHOW FULL COLUMNS; Documentació 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). Documentació 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: [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. Exemple 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. 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. 📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web. 🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez