DB9-Implementierung

CREATE TABLE SPEND_STAT
(
S_STATUS VARCHAR2(1),
STAT_TXT VARCHAR2(15),
PRIMARY KEY (S_STATUS) ENABLE
);
3.3. Implementierung in SQL
DDL-Grundlagen
Constraint-Verzögerung
Implementierungs-Strategien
DDL: Data Definition Language
• Untermenge von SQL
• Manipulation des Data Dictionary:
– Tabellendefinition und –änderung
CREATE TABLE, ALTER TABLE, DROP TABLE
– Sichtendefinition und –änderung
CREATE VIEW, ALTER VIEW, DROP VIEW
– Definition und Änderung von
Integritätsbedingungen (werden Tabellen
zugeordnet)
(c) schmiedecke 06
DbMz 8 - Implementierung
2
Tabellendefinition
create table <tabname>
(<spalte1> <datentyp>
…,
<spalte n><datentyp>
);
Datentypen (Oracle-spezifisch) in Auswahl
CHAR(length)
Zeichenkette fester Länge
VARCHAR2(length)
Z. variabler Länge
NUMBER(precision, scale)
Gleitpunktzahl mit pr, Stellen und sc.
Nachkommastellen
INT
Ganzzahl bis 38 Stellen
DATE
Zeitangabe Jahr - Sekunde
CLOB, BLOB
Character oder Binary Large Object
BFILE
Zeiger auf eine externe Datei
(c) schmiedecke 06
DbMz 8 - Implementierung
3
Tabelle ändern oder löschen
Spalte hinzufügen:
alter table <tabname>
add <spalte n+1> <datentyp>;
Spaltentyp ändern:
alter table <tabname>
modify <spalte m> <datentyp>;
Spalte entfernen:
alter table <tabname>
drop column <spalte m>;
Tabelle löschen:
drop table <tabname>;
Tabelle umbenennen:
rename <tabname> to <tabname-neu>;
(c) schmiedecke 06
DbMz 8 - Implementierung
4
Schlüsseldefinition
Primärschlüsseldefinition:
create table <tabname>
(<spalte1> <datentyp> )
[ [ constraint <constrname> ] primary key
],
…,
<spalte n> ….
create table <tabname>
create table <tabname>
(<spalte1> <datentyp>, ..., <spalten><datenty>)
[ [ constraint <constrname> ]
primary key(<spalte1>,…,<spalte m>)
];
(c) schmiedecke 06
DbMz 8 - Implementierung
5
Schlüsseldefintion
Fremdschlüsseldefinition
];
create table <tabname>
(<spalte1> <datentyp>
[ [ constraint <constrname> ]
references <tabname> [<spalte>]
]
, …,
<spalte n> ….
[ [constraint <constrname>]
foreign key (<spalte1>,…,<spalte m>)
references <tabname> [(<spalte1>,…<spalte m>)]
(c) schmiedecke 06
DbMz 8 - Implementierung
6
Weitere Integritätsbedingungen
(Constraints)
Constraints:
primary key
foreign key
check
not null
unique
Constraint hinzufügen:
alter table <tabname>
add constraint
[<constraint-name>] constraint
Constraints ändern:
...drop constraint <constraint-name>
disable constraint <constraint-name>
enable constraint <constraint-name>
(c) schmiedecke 06
DbMz 8 - Implementierung
7
Inkonsistente Zwischenzustände
• Referentielle Integrität gefährdet bei:
– INSERT:
Eintrag eines "Kindes" vor dem "Vater" unzulässig
– UPDATE:
Ändern eines Primärschlüssels macht Referenzen ungültig
– DELETE:
Löschen des "Vaters" hinterlässt "Waisen"
• Inkonsistente Zwischenzustände nicht immer vermeidbar:
– Existenzabhängigkeit fordert "not null"-Fremdschlüssel
– Was wird bei einer Spende zuerst eingetragen, der Spender oder
die Konserve?
– Problem auch bei abgeleiteten Attributen
Spender
(c) schmiedecke 06
(1,*)
(1,1)
Konserve
DbMz 8 - Implementierung
8
Umgang mit inkonsistenten
Zwischenzuständen
• Spezifizierte "referentielle Aktionen" der DB:
– Restriktives Vorgehen
– automatische Anpassung
• Verzögerte Bedingungsprüfung:
– um inkonsistente Zwischenzustände zu "überbrücken"
(c) schmiedecke 06
DbMz 8 - Implementierung
9
Referentielle Aktionen
NO ACTION
– Operation wird grundsätzlich zurückgewiesen
RESTRICT (nicht in Oracle)
– Ausführung nur, wenn keine tatsächlichen Referenzen
vorhanden
CASCADE
– Änderung wird in die referenzierenden Datensätze
übernommen
SET NULL / SET DEFAULT (nicht in Oracle/MySQL)
– referenzierender Fremdschlüssel wird auf null /
Standardwert gesetzt
(c) schmiedecke 06
DbMz 8 - Implementierung
10
Spezifikation referentieller Aktionen
• Spezifikation erfolgt bei der FS-Definition
on delete | update referentielleAktion
create table spender
(s_id number(6) primary key,
erstspende number(6) not null,
foreign key (erstspende) references konserve
on delete set 999999
-- erfordert unlöschbaren Datensatz 999999 in konserve
);
(So weder in MySQL noch in Oracle umsetzbar – nur NULL-Setzen
möglich)
(c) schmiedecke 06
DbMz 8 - Implementierung
11
Verzögerte Constraints-Prüfung
Verzögerte Constraint-Prüfung (nicht in MySQL):
–
bedeutet, dass die Integritätsbedingung immer erst
am Ende einer Transaktion (commit) geprüft wird.
–
muss bei der Constraint-Definition vorgesehen werden
–
ggf. Constraint löschen und neu defineren
Verzögerte Constraint-Prüfung definieren:
constraint <constraint-name> constraint
deferrable [initially immediate | deferred]
Verzögerung an/abschalten
bezüglich der aktuellen Transaktion / Sitzung (ab Oracle 10g):
set constraints
<constraint-name>,…,<constraint-name> deferred;
set constraints
ALL immediate;
(c) schmiedecke 06
DbMz 8 - Implementierung
12
Constraints deaktivieren
• Constraints können beliebig ein- und
ausgeschaltet werden (nur einzeln, nicht MySQL):
alter table table
{ enable | disable } constraint <constraint-name>;
• In MySQL kann die Überprüfung der referentiellen Integrität global
an- und ausgeschaltet werden:
set foreign_key_checks = 0; # ausschalten
set foreign_key_checks = 1; # einschalten
(c) schmiedecke 06
DbMz 8 - Implementierung
13
Einblick ins Data Dictionary
(in Oracle)
• Alle DDL-Befehle wirken aufs Data Dictionary
• Dieses befindet sich in den Tabellen:
– user_tabs
– user_tab_columns
– user_constraints
• Lesen üben:
Schreibe das DB-Schema aus dem DD ab!
(c) schmiedecke 06
DbMz 8 - Implementierung
14
Einblick ins Data Dictionary
• Tabellendefinitionen und aktueller
Status nicht mehr trivial
• Grundforderung: DD im DBMS mit
gespeichert und mithilfe der
Datenbanksprache abfragbar
• Wie heißen die Tabellen des DD?
(c) schmiedecke 06
DbMz 8 - Implementierung
15
DD-Views in Oracle
Präfix: ALL_ / USER_
•
•
•
•
•
•
•
•
•
USER_TABLES
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_COL_COMMENTS
USER_CONSTRAINTS
USER_DEPENDENCIES
USER_INDEXES
USER_ROLE_PRIVS
USER_FREE_SPACE
(c) schmiedecke 06
DbMz 8 - Implementierung
16
Benutzung
• SELECT * FROM USER_TAB_COLS
WHERE TAB_NAME = 'Spender';
• DESCRIBE TABLE 'Spender';
• SELECT Constraint_Name, Status, Deferrable
FROM USER_CONSTRAINTS;
(c) schmiedecke 06
DbMz 8 - Implementierung
17
DD-Ansichten in MySQL
SHOW-Befehl
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name]
[LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
(c) schmiedecke 06
DbMz 8 - Implementierung
18
Implementierungs-Strategien
• Maßnahmen zum "unkomplizierten"
Implementieren eines DB-Schemas:
– Zuerst alle Tabellen definieren.
– Danach alle Fremdschlüssel definieren
– (in einem Script:
ganz zuerst alle Tabellen löschen
wiederholbare Ausführung)
• Oder:
– zuerst alle Fremdschlüssel deaktivieren am
Schluss alle aktivieren
(c) schmiedecke 06
DbMz 8 - Implementierung
19
Prototyp-Strategie
• Maßnahme zum unkomplizierten Eintragen
von Test-Datensätzen:
– zuerst alle Constraints deaktivieren (disable)
– nach dem Eintragen alle aktivieren (enable)
(c) schmiedecke 06
DbMz 8 - Implementierung
20
(c) schmiedecke 06
DbMz 8 - Implementierung
21