7. SQL constraints og triggers

1. 7. SQL constraints og triggers Aktive elementer i SQL 7. SQL constraints og triggers

2. Constraints, 315 • Regler om data • Erklæres over for DBMS'en. • DBMS'en checker løbende at regler overholdes ved • insert • delete • update • Brud på regler  DBMS nægter at udføre insert / delete / update, der bryder reglen. 7. SQL constraints og triggers

3. Constraints II, 315 • Regler kan også implementeres i de applikationer, der bruger databasen. • Konsekvens: alle applikationer skal checke regler - altid! • Konsistens: alle applikationer skal være enige om reglerne Applik. 1 Applik. 2 Database 7. SQL constraints og triggers

4. Typer af constraints, 315 • key constraint • mængde af attributter, der identificerer hver række i en tabel • foreign key constraint • mængde af attributter, der referer til en key i en [anden] tabel • generelle constraints • på hver række • land in ('DK', 'US') • på hele tabellen 7. SQL constraints og triggers

5. Syntaks 1: create table person ( cpr char(11) primary key, … ); Attribute level. Anvendes hvis nøglen indeholder 1 attribut. Eks. fig. 7.1, side 317 Syntaks 2: create table studKursus ( studCpr char(11), kursusnr number(1000), primary key (studCpr, kursusnr) ); Table level. Anvendes hvis nøglen indeholder flere attributter. Eks, fig. 7.2, side 316 SQL primary key, 316 7. SQL constraints og triggers

6. SQL unique, 317 • max. 1 primary key pr. tabel • andre unikke mængder af attributter kan markeres unique. • syntaks • attribute level • hvis en enkelt attribut er unique • table level • hvis flere attributter tilsammen er unique. 7. SQL constraints og triggers

7. primary key og unique skaber indekser, 318 • DBMS laver indeks for hver primary key og hver unique • mange "gratis" indekser • DBMS checker ved hvert insert / update • om nye data overholder primary key / unique ved at indsætte / opdatere unikt indeks. 7. SQL constraints og triggers

8. Fremmednøgler, 319 • fremmednøgle • en mængde af attributter, der referer til primærnøglen / unique i en [anden] tabel • Regel • Det refererede skal eksistere • Ingen dangling tuples [side 323] • Sådan er det ikke på www - der er mange "løse" links. • DBMS checker ved insert, update og delete • Fremmednøglen må dog være null, dvs. ikke referere til nogen. 7. SQL constraints og triggers

9. SQL fremmednøgler, 320 create table Person ( cpr char(11) primary key, … postnr number(4), foreign key (postnr) references postby (postnr) ); • Eks. 7.3, side 320 • MySQL • DBMS, der bl.a. bruges til web-applikationer. • Default: Tillader at man skriver foreign key …, men det har ingen effekt {DBMS checker ikke om det refererede eksisterer} • http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html 7. SQL constraints og triggers

10. Reference integritet, 321 • Generel regel: • Det refererede skal eksistere. • Hvad sker der hvis man forsøger at slette / ændre data, som andre referer til? • Nægt sletning / ændring! • Cascade politik • on delete set null / cascade • on update cascade • Eks. 7.4, side 322 7. SQL constraints og triggers

11. Cascade politik, 322 • http://isl.ruc.dk/oradoc92/server.920/a96540/clauses3a.htm#1002160 • on delete set null • alle fremmednøgle, der referer til det slettede, skal sættes til null. • on delete cascade • alle rækker med fremmednøgler, der referer til det slettede, skal også slettes!! • on update cascade • alle fremmednøgler, der referer til det ændrede, skal også ændres. • findes ikke i Oracle [primærnøgler bør ikke være databærende og skal derfor ikke ændres!] 7. SQL constraints og triggers

12. not null attribut datatype not null værdien null forbydes i denne række checkes af DBMS eks. side 328 check attribut datatype check (betingelse) værdierne i denne kolonne skal altid overholde betingelsen checkes af DBMS eks. side 329 + 331 Constraints på attributter, 327 7. SQL constraints og triggers

13. Navngivning af constraints, 334 • Constraints bør navngives • ellers kan man [næsten] ikke senere ændre / slette dem • eks. 7.11, side 334 • DBMS navngiver anonyme constraints • Oracle: Alle dine constraints på tabellen Student an ses vha. • select * from user_constraints where table_name = 'STUDENT'; • User_constraints er en såkaldt system-tabel, der vedligeholdes af Oracle. 7. SQL constraints og triggers

14. Ændring af constraint, 334 • alter table … • ændrer på en tabel • kan også ændre / slette / tilføje constraints • eks. 7.12, side 334-335 7. SQL constraints og triggers

15. Gensidig afhængighedHvad kom først: Hønen eller ægget? • Fra www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html • Ved kreering af en tabel med foreign key skal den refererede tabel være kreeret. • Problem • Hvis tabel A refererer til tabel B og vice versa. Hvilken tabel (A eller B) skal så kreeres først? • Løsning • create table A uden foreign key til B • create table B med foreign key til A • alter table A add foreign key til B 7. SQL constraints og triggers

16. Gensidig afhængighed, eksempel create table employee ( empID number(3) primary key, deptID number(3) ) ------------------------- create table department ( deptID number (3) primary key, bossID number (3), foreign key (bossID) refers to employee (empID) initially deferred deferrable ) ------------------------ alter table employee add constraint emp_dept_fk foreign key (deptID) refers to department (deptID) initially deferred deferrable 7. SQL constraints og triggers

17. Gensidig afhængighed, insert, 323 • Problem • Man kan ikke indsætte første række i Employee uden at referere til Department og vice versa • Løsning • foreign key erklæres "deferrable initially deferred" • Fremmednøgler checkes ikke ved insert, men først ved senere commit (så man kan nå at indsætte i begge tabeller inden commit) • Eks. 7.6, side 325 7. SQL constraints og triggers

18. Assertions, 336 • Checks vs. assertions • Tabel side 339 • Eks. side 329 nederst • Assertion syntaks • create assertionnavn check (betingelse) • Eksempler • Fig. 7.6, side 338 • Assertions skal checkes ved hvert ændring (insert, update, delete). Det tager tid! 7. SQL constraints og triggers

19. Triggers, 340 • Lille "program", der udføres ved en bestemt begivenhed (event) {insert, update, delete} • Eks. fig. 7.8, side 341 • update / insert / delete ofattributontabel • begivenheden • before / after begivenheden • adgang til både nye og gamle data • when betingelse • trigger udføres kun hvis betingelse er sand. • Eks. fig. 7.9, side 343 7. SQL constraints og triggers

20. Instead-Of triggers, 344 • Ikke med i SQL-99 standarden • Men interessant alligevel. • Er med i Oracle. • Trigger udføres i stedet for egentlige begivenhed. • Godt til opdatering af views • Trigger "griber" opdatering af views og opdaterer i stedet basis tabellerne. • Eks. 7.17, side 344-345 7. SQL constraints og triggers