Relationsdatabaser og SQL

1. BentMøllerMadsen Relationsdatabaser og SQL Del 2 af 4: Data Definition Language (DDL) i SQL Aalborg Universitet, d. 4. september 2006

2. Tegn-forklaring • Denne skrifttype er lig kode • STORE BOGSTAVERer lig Oracle kommandoer • <tekst> betyder at tekst inkl. større-end og mindre-end tegn skal erstattes med den faktiske tekst/værdi. • [tekst] betyder at tekst er valgfri og kan udelades. De firkantede parenteser skal aldrig skrives i koden. • {tekst1|tekst2} betyder at en af teksterne adskilt af de lodrette streger skal vælges. De krøllede parenteser skal aldrig skrives i koden.

3. Elementer i en tabel Postnumre Postnumre

4. SQL til tabelstruktur • Data Definition Language (DDL) • CREATE Opret tabel • ALTER Ændre tabel • DROP Nedlæg tabel • (DESCRIBE Beskriv tabel) • (RENAME Omdøb tabel) • Vedrører tabelstrukturen ikke de faktiske data i tabeller Postnumre

5. Oprettelse af tabel • CREATE TABLE <tabelnavn> (<kolonnenavn> <DATATYPE> [DEFAULT <værdi>] [<CONSTRAINTS>]); CREATE TABLE kunder ( kunde_id NUMBER(10), kunde VARCHAR2(20), adresse VARCHAR2(50), postnr NUMBER(4) DEFAULT 9000 ); tabelnavn maks. længde kolonnenavn standardværdi datatype

6. Tabel- og kolonnenavn • Tabel og kolonnenavne skal: • begynde med et bogstav • bestå af mellem 1 og 30 karakterer • lovlige karakterer: A-Z, 0-9, _, $, # • undgå æ, ø eller å • være entydige / unikke • Ikke to tabeller af samme navn • Ikke to kolonner af samme navn i én tabel • ikke være et reserveret ord, fx kommandoer (SELECT, TABLE, BY m.fl.) • Der skal normalt ikke tage ikke hensyn til STORE og små bogstaver • Hvis navnene skrives i dobbelt anførselstegn, kan næsten alle reglerne dog brydes!!!

7. Generelle datatyper • Tekst • Tal • Dato/tid • Binære filer • Lyd • Billeder • Dokumenter • Boolsk type (Boolean) • Sand, falsk eller null (tom) • Findes ikke i Oracle (i stedet kan en almindelig tekstkolonne med disse værdier anvendes)

8. Oracle datatyper • CHAR(maks. længde) • Fast karakterfelt med plads til maks. 2000 tegn • VARCHAR2(maks. længde) • Variabelt karakterfelt med plads til maks. 4000 tegn • NUMBER[(præcision[,skala])] • Tal, hvor præcision er antal tilladte cifre, mens skala er antal decimaler ud af det samlede antal cifre. • DATE • Datofelt med præcision ned til 1 sekund • TIMESTAMP • Datofelt med præcision ned til brøkdele af sekunder

9. Oracle datatyper • CLOB • Character Large Object • Tekstfelt med plads til 4 GB • BLOB • Binary Large Object • Binært felt med plads til 4 GB

10. Beskrive & slette tabeller m.m. • Beskrive én tabel • DESCRIBE <tabelnavn>; • DESC <tabelnavn>; • Vis alle tabeller • SELECT * FROM user_tables; • Omdøbe tabel • RENAME <tabelnavn> TO < nyt tabelnavn>; • Slette tabel • DROP TABLE <tabelnavn>;

11. Betingelser / constraints • Udover at datatypen afgrænser mulighederne for at indtaste data er der 5 typer betingelser, der kan knyttes på en eller flere kolonner i tabellerne for at sikre dataintegritet. • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY …… REFERENCES …… • CHECK • Alle betingelser kan enten oprettes direkte på én kolonne eller selvstændigt med mulighed for at omfatte flere kolonner.

12. NULL / NOT NULL • NULL er lig en ukendt værdi • Med en NOT NULL constraint på en kolonne bliver det et krav, at der altid skal være en værdi i kolonnen. CREATE TABLE kunder (kunde_id NUMBER(10), kunde VARCHAR2(20), adresse VARCHAR2(50) [CONSTRAINT adresse_nn] NOT NULL, postnr NUMBER(4), [CONSTRAINT adresse_nn] CHECK (adresse IS NOT NULL)); Not null

13. UNIQUE • En UNIQUE constraint på en eller flere kolonner betyder, at værdierne i disse skal være unikke på tværs af alle rækker • Der må godt forekomme tomme/null felter CREATE TABLE kunder (kunde_id NUMBER(10), kunde VARCHAR2(20) [CONSTRAINT kunde_uk] UNIQUE, adresse VARCHAR2(50) NOT NULL, postnr NUMBER(4), [CONSTRAINT kunde_uk] UNIQUE (kunde)); Unique (key)

14. Primærnøgle • En primærnøgle anvendes til unikt at identificerer hver række i en tabel • Den kan bestå af flere kolonner (en sammensat primærnøgle) • Krav til en primærnøgle: • Der kan kun være én for hver tabel • Alle data i primærnøglen skal være unikke • Der må ikke forekomme tomme (null) felter • Yderligere bør data være forholdsvis ”stabile” • Der kan være flere kandidatnøgler, der opfylder ovenstående krav, til en primærnøgle.

15. Primærnøgle • Overvejelser omkring at anvende naturlige eller syntetiske/surrogat data til primærnøgler CREATE TABLE kunder ( kunde_id NUMBER(10) [CONSTRAINT kunder_pk] PRIMARY KEY, kunde VARCHAR2(20) UNIQUE, adresse VARCHAR2(50) NOT NULL, postnr NUMBER(4), [CONSTRAINT kunder_pk] PRIMARY KEY (kunde_id) ); Primærnøgle

16. Fremmednøgle • Fremmednøgler sikrer integriteten mellem tabeller • Krav til en fremmednøgle: • Fremmednøglen skal referere til en primær eller unik nøgle. • En fremmednøgle-værdi skal enten kunne findes i reference-tabellen eller være NULL. • En kolonnen med en fremmednøgle skal være samme datatype som kolonnen med den primære eller unikke nøgle i reference-tabellen. • En fremmednøgle kan referere til den tabel, som den selv er placeret i. (Rekursivt hierarki)

17. Fremmednøgle Kunder Postnumre • ALTER TABLE kunder ADD [CONSTRAINT kunder_postnumre_fk] FOREIGN KEY (postnr) REFERENCES postnumre (postnr) [ON DELETE {CASCADE|SET NULL}];

18. Fremmednøgle • CREATE TABLE kunder ( kunde_id NUMBER(10) PRIMARY KEY, kunde VARCHAR2(20) UNIQUE, adresse VARCHAR2(50) NOT NULL, postnr NUMBER(4) [CONSTRAINT kunder_postnumre_fk] REFERENCES postnumre (postnr) [ON DELETE {CASCADE|SET NULL}], [CONSTRAINT kunder_postnumre_fk] FOREIGN KEY (postnr) REFERENCES postnumre (postnr) [ON DELETE {CASCADE|SET NULL}] ); Fremmednøgle

19. Fremmednøgle • Ingen ON DELETE regel på fremmednøglen • Det er ikke tilladt at slette rækker fra tabellen som fremmednøglen refererer til (postnumre), hvis de anvendes i tabellen med fremmednøglen (kunder). • ON DELETE CASCADE regel på fremmednøglen • Sletning af rækker fra tabellen, som fremmednøglen refererer til (postnumre), medføre at sammenhængende rækker i tabellen med fremmednøglen (kunder) også bliver slettet. • ON DELETE SET NULL regel på fremmednøglen • Sletning af rækker fra tabellen, som fremmednøglen refererer til (postnumre), gør at værdier i kolonnen med fremmednøglen sættes til null for sammenhængende rækker.

20. CHECK • CHECK Constraints er fleksible betingelser, man selv definerer, som data skal opfylde. CREATE TABLE kunder ( kunde_id NUMBER(10) PRIMARY KEY, kunde VARCHAR2(20) UNIQUE, adresse VARCHAR2(50) NOT NULL, postnr NUMBER(4) REFERENCES postnumre (postnr) [CONSTRAINT postnr_laengde_cc] CHECK (LENGTH(postnr) = 4), [CONSTRAINT postnr_laengde_cc] CHECK (LENGTH(postnr) = 4) ); Check constraint

21. Ændre tabeller • ALTER TABLE • ADD - tilføje kolonne / constraint • MODIFY - ændre kolonne / constraint • DROP - slette en kolonne / constraint • RENAME - omdøbe kolonne / constraint • ENABLE/DISABLE- deaktivere/aktivere constraint

22. Ændre tabel – kolonne eksempler • ALTER TABLE kunder ADD ( tlf NUMBER(8), email VARCHAR2(30) ); • ALTER TABLE kunder MODIFY ( tlf VARCHAR2(15) NOT NULL, email VARCHAR2(50) ); • ALTER TABLE kunder DROP (tlf, email); • ALTER TABLE kunder RENAME COLUMN tlf TO telefon;

23. Ændre tabel – constraint eksempler • ALTER TABLE kunder ADD CONSTRAINT email_cc CHECK (email LIKE '%@%'); • ALTER TABLE kunder MODIFY CONSTRAINT email_cc {ENABLE|DISABLE}; • ALTER TABLE kunder DROP CONSTRAINT email_cc; • ALTER TABLE kunder RENAME CONSTRAINT email_cc TO email_check; • ALTER TABLE kunder {ENABLE|DISABLE} CONSTRAINT email_cc; Samme resultat

24. Vis tabeller og constraints • Vis alle tabeller • SELECT * FROM user_tables; • Vis alle constraints • SELECT * FROM user_cons_columns NATURAL JOIN user_constraints; • Tømning af ”Papirkurv” • Hvis der optræder navne lignende følgende i ovenstående forespørgsler • BIN$EO/cSFBIQqOsWWy9uhmgFQ==$0 • så tøm Oracles papirkurv med denne kommando • PURGE RECYCLEBIN;

25. ?