Webinar@Lunchtime: Plausibilitätsprüfungen mit Integrity
Werbung
WEBINAR@LUNCHTIME THEMA: PLAUSIBILITÄTSPRÜFUNGEN MIT INTEGRITY CONSTRAINTS IN BASE SAS® KARIN GROSS C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . WEBINAR@LUNCHTIME HERZLICH WILLKOMMEN BEI WEBINAR@LUNCHTIME Moderation Training Anne K. Bogner-Hamleh Karin Gross SAS Institute GmbH Education Consultant SAS Institute GmbH Manager SAS Education Services Xing-Profil: Xing-Profil: http://www.xing.com/profile/AnneKatrin_BognerHamleh?key=0.0 http://www.xing.com/profile/Karin_Gross2 Hinweise zum Ablauf des Webinars: • Teilnehmer sind automatisch “stumm” geschaltet • Sie können Nachrichten an den Moderator senden und Fragen stellen • die Veranstaltung wird aufgezeichnet C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . WEBINAR@LUNCHTIME PLAUSIBILITÄTSPRÜFUNGEN MIT INTEGRITY CONSTRAINTS IN BASE SAS Agenda • Grundlagen Integrity Constraints • Constraint Typen • • Allgemeine Constraints Referenzielle Constraints • Definition im PROC DATASETS und PROC SQL • IC dokumentieren C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . WEBINAR@LUNCHTIME FRAGE ZU PLAUSIBILITÄTSPRÜFUNGEN C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FRAGE Antworten C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FRAGE Wie überprüfen Sie die Plausibilität Ihrer Daten? a) b) c) d) e) Unsere Daten sind sauber! Haufenweise IF-Anweisungen Kolleg(inn)en Das Genie beherrscht das Chaos. Ausgesprochen ungern. INTEGRITY CONSTRAINTS GRUNDLAGEN • Integrity Constraints zur Plausibilisierung • Zwingend erforderlich bei manuellen Eingaben • Alte Regel: „Mach‘s gleich richtig!“ • Reduziert spätere aufwändige Fehlersuche • Daten bleiben auch plausibel C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • Referenzielle Constraints • • C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY PRIMARY KEY FOREIGN KEY INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY Referenzielle Constraints • • PRIMARY KEY FOREIGN KEY Variable muss Werte enthalten C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY Referenzielle Constraints • • PRIMARY KEY FOREIGN KEY Variable muss Bedingung erfüllen C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY Referenzielle Constraints • • PRIMARY KEY FOREIGN KEY Variable muss eindeutige Werte enthalten (fehlende Werte sind zulässig) C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • Referenzielle Constraints • • C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY PRIMARY KEY FOREIGN KEY WEBINAR@LUNCHTIME FRAGE ZUM PRIMARY KEY C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FRAGE FRAGE In der Datei work.frage wird die Variable ID als Primary Key definiert. Welcher ID Wert wird ohne Fehlermeldung angehängt? ID 1 3 5 7 Antworten C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . a) b) c) d) 1 . beide keiner FRAGE FRAGE In der Datei work.frage wird die Variable ID als Primary Key definiert. Welcher ID Wert wird ohne Fehlermeldung angehängt? ID 1 3 5 7 Antworten C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . a) b) c) d) 1 . beide keiner Der Wert darf nicht fehlen und muss eindeutig sein. INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY Referenzielle Constraints • • PRIMARY KEY FOREIGN KEY Primary Key auf “Parent Tabelle” (Referenztabelle) C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS CONSTRAINT TYPEN • Allgemeine Constraints • • • • • NOT NULL CHECK UNIQUE / DISTINCT PRIMARY KEY Referenzielle Constraints • • PRIMARY KEY FOREIGN KEY Foreign Key auf “Child-Tabelle” (abhängige Tabelle) Benennung der Parent-Tabelle (mit PK) Umgangs mit Änderungen/Löschungen in der ParentTabelle C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS FOREIGN KEY Customers (Parent) C_ID ist Primary Key Foreign Key verweist auf Primary Key C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … INTEGRITY CONSTRAINTS FOREIGN KEY Customers (Parent) C_ID ist Primary Key Schlechten Wert in der Child-Tabelle hinzufügen C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 5 … INTEGRITY CONSTRAINTS FOREIGN KEY Customers (Parent) C_ID ist Primary Key Guten Wert in der Child-Tabelle hinzufügen C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 3 … INTEGRITY CONSTRAINTS Wert in der ParentTabelle ändern C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FOREIGN KEY • ON UPDATE RESTRICT Der Wert wird nur dann geändert, wenn keine Foreign Keys aus der Child-Tabelle diesen Primary Key referenzieren. • ON UPDATE SET NULL Der Wert wird geändert, Foreign Key-Werte aus der ChildTabelle, die diesen Primary Key referenzieren, werden auf Missing gesetzt. • ON UPDATE CASCADE Der Wert wird geändert, zugehörige Foreign Key-Werte aus der Child-Tabelle werden ebenfalls geändert. INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE ÄNDERN Customers (Parent) C_ID ist Primary Key ON UPDATE RESTRICT C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 3 … INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE ÄNDERN Customers (Parent) C_ID ist Primary Key ON UPDATE RESTRICT C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 99 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 3 … INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE ÄNDERN Customers (Parent) C_ID ist Primary Key ON UPDATE SET NULL C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 3 … INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE ÄNDERN Customers (Parent) C_ID ist Primary Key ON UPDATE SET NULL C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 99 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 . … INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE ÄNDERN Customers (Parent) C_ID ist Primary Key ON UPDATE CASCADE C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 3 … INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE ÄNDERN Customers (Parent) C_ID ist Primary Key ON UPDATE CASCADE C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 99 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 99 … INTEGRITY CONSTRAINTS FOREIGN KEY • Wert in der ParentTabelle löschen ON DELETE RESTRICT Zwingt den Benutzer die entsprechenden Foreign Key-Werte aus der Child-Tabelle zu löschen, bevor er den Primary KeyWert aus der Parent Tabelle löschen kann. • ON DELETE SET NULL Setzt die Foreign Key-Werte in der Child-Tabelle auf Missing, wenn ein Primary Key-Wert aus der Parent-Tabelle gelöscht wird. C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE LÖSCHEN Customers (Parent) C_ID ist Primary Key ON DELETE RESTRICT C_ID … 1 … 2 … 3 … Keine Löschung möglich! C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 3 … INTEGRITY CONSTRAINTS FOREIGN KEY – WERT IN DER PARENT-TABELLE LÖSCHEN Customers (Parent) C_ID ist Primary Key ON DELETE SET NULL C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . C_ID … 1 … 2 … 3 … Orders (Child) C_ID ist Foreign Key Order _ID C_ID … A1 2 … A2 1 … A3 1 … A4 2 … A5 . … INTEGRITY CONSTRAINTS PROC SQL PROC DATASETS C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS DOKUMENTIEREN U. LÖSCHEN proc sql; describe table constraints work.Orders_ab_Dez2015; alter table work.Orders_ab_Dez2015 drop constraint Eindeutige_OrderID; proc datasets lib=work nolist; contents work.Orders_ab_Dez2015; modify work.Orders_ab_Dez2015; ic delete Eindeutige_OrderID; FRAGEN? C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS NOT NULL CHECK UNIQUE C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . SYNTAXBEISPIELE ALLGEMEINE CONSTRAINTS PROC DATASETS ic create Datum_vorhanden = not null (Order_Date) message= 'Wert darf nicht leer sein.' msgtype=user; ic create richtige_Reihenfolge = check (where=(Order_Date <= Delivery_Date)) message= 'Falsche Reihenfolge.' msgtype=user; ic create Eindeutige_OrderID = unique (Order_ID) message = 'Order ID bereits vorhanden.' msgtype=user; INTEGRITY CONSTRAINTS NOT NULL CHECK UNIQUE C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . SYNTAXBEISPIELE ALLGEMEINE CONSTRAINTS PROC SQL alter table work.Orders_ab_Dez2015_SQL add constraint datum_vorhanden not null (Order_Date) message= 'Wert darf nicht leer sein.' msgtype=user add constraint richtige_Reihenfolge check (Order_Date <= Delivery_Date) message= 'Falsche Reihenfolge.' msgtype=user add constraint Eindeutige_OrderID unique (Order_ID) message = 'Order ID bereits vorhanden.' msgtype=user; INTEGRITY CONSTRAINTS SYNTAXBEISPIELE REFERENZIELLE CONSTRAINTS PROC DATASETS PRIMARY KEY modify customer_dim; ic create KdNr_PK = primary key (Customer_ID) message = 'Primärschlüsselbedingung für Customer ID verletzt.' msgtype=user; FOREIGN KEY modify orders; ic create KdNr_FK = foreign key (Customer_ID) references work.customer_dim message= 'Kd.Nr. nicht in Kundendaten vorhanden.' on update cascade on delete restrict msgtype=user; C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . INTEGRITY CONSTRAINTS PRIMARY KEY FOREIGN KEY C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . SYNTAXBEISPIELE REFERENZIELLE CONSTRAINTS PROC SQL alter table work.customer_dim; add Constraint KdNr_PK primary key (Customer_ID) message = 'Primärschlüsselbedingung für Customer ID verletzt.' msgtype=user; alter table work.orders add Constraint KdNr_FK foreign key (Customer_ID) references work.customer_dim on update cascade on delete restrict message= 'Kd.Nr. nicht in Kundendaten vorhanden.' msgtype=user; WEBINAR@LUNCHTIME VIELEN DANK FÜR IHRE TEILNAHME Interesse an weiterem Austausch? • Diskutieren Sie mit uns in der XING-Gruppe Business Analytics mit SAS • Sprechen Sie uns direkt an: [email protected] Haben Sie schon mal an einer SAS Live Web Class teilgenommen? Sie vereint die Interaktivität einer Präsenztrainings mit den Vorteilen einer Online-Schulung. Die Themen und Termine der Live Web Classes 2017 C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . WEBINAR@LUNCHTIME WEITERE INFORMATIONEN UND KURSE ZU DIESEM THEMA… SAS® SQL 1: Grundlagen 30.01. – 01.02.17 Heidelberg 10.04. – 12.04.17 Zürich 07.06. – 09.06.17 Heidelberg Creating Integrity Constraints (PDF) Zertifizierungspakete – Sparen Sie bis zu 30% SAS® Certification Practice Exam: Base Programming for SAS® 9 SAS® Certification Practice Exam: Advanced Programming for SAS® 9 C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . NÄCHSTES WEBINAR@LUNCHTIME: 23. JANUAR 2017 GELTUNGSBEREICHE VON MACRO-VARIABLEN KARIN GROSS C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FOLIEN ZUM DOWNLOAD UNTER WWW.SAS.DE/LUNCHTIME WIE HAT IHNEN UNSER WEBINAR GEFALLEN? C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . www.SAS.com
