2 Tabellen mit 1:1-Beziehung synchronisieren?
Frage
Antworten
-
Hallo Knorpi,
Du hast Recht - die Fremdschlüsseleinschränkung bezieht sich nur die Detailtabelle aber nicht auf die Mastertabelle. Wie Benjamin bin ich selbst auch immer etwas skeptisch in Bezug auf Trigger. Sie haben einige Nachteile, die halt abgewogen werden müssen (Dabei lasse ich mal die Probleme aus Sicht eines Programmierers aussen vor!):
- Trigger erzeugen temporäre Objekte (INSERTED und DELETED) in TEMPDB
- Trigger sind Bestandteil der Benutzertransaktion und blockieren Ressourcen, die im Trigger angesprochen werden
- Die Verwendung von Triggern führt häufig zu Deadlock-Situationen, wenn im Trigger weitere Objekte angesprochen werden (wie in Deinem Beispiel)
Dein Beispiel als Referenz sieht Punkt 2 der "Bedenken" wie folgt aus:
CREATE TABLE dbo.personal ( idx int NOT NULL PRIMARY KEY CLUSTERED, fname char(50) NOT NULL, lname char(50) NOT NULL ); CREATE TABLE dbo.personal_detail ( personal_idx int NOT NULL PRIMARY KEY CLUSTERED, detail_1 int NOT NULL DEFAULT (0), detail_2 int NOT NULL DEFAULT (0), CONSTRAINT fk_personal FOREIGN KEY (personal_idx) REFERENCES dbo.personal (idx) ON DELETE CASCADE ); GO -- Insert ONE record to avoid system blocked resources -- when locks are investigated INSERT INTO dbo.personal (idx, fname, lname) VALUES (1, 'Uwe', 'Ricken'); GO -- Create the trigger for insertion of data in reference table CREATE TRIGGER dbo.trg_personal_io_insert ON dbo.personal INSTEAD OF INSERT AS SET NOCOUNT ON; -- Insert data from personal INSERT INTO dbo.personal SELECT * FROM inserted; -- Insert data into referencial table INSERT INTO dbo.personal_detail (personal_idx) SELECT i.idx FROM inserted AS i LEFT JOIN dbo.personal_detail AS d ON (i.idx = d.personal_idx) WHERE d.personal_idx IS NULL; SET NOCOUNT OFF; GO
Das obige Beispiel habe ich aus Deinen Anforderungen entnommen und den entsprechenden Trigger (hätte auch ein INSERT-Trigger sein können) implementiert. Nun wird ein weiterer Datensatz eingetragen. Dabei wird aber dieser Vorgang in einer expliziten Transaktion ausgeführt. Ich lasse die Transaktion zunächst geöffnet!
BEGIN TRANSACTION insertdata; GO INSERT INTO dbo.personal (idx, fname, lname) VALUES (2, 'Beate', 'Ricken'); SELECT resource_type, CASE WHEN resource_type = N'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id) ELSE CAST(resource_associated_entity_id AS nvarchar(255)) END AS resource_type, request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID;
Der erste Teil der Abfrage fügt in die Tabelle dbo.personal einen neuen Datensatz ein. Im zweiten Statement überprüfe ich, welche Ressourcen durch die Benutzertransaktion blockiert sind.
An der Abbildung kannst Du sehr schön erkennen, dass Ressourcen beider Tabellen blockiert werden. Solange die Transaktion nicht abgeschlossen ist, kann keine der beiden Ressourcen freigegeben werden und andere Prozesse müssen warten.
1:1-Beziehungen machen - entgegen der Meinung von Benjamin - in manchen Szenarien sicherlich Sinn. Zum Beispiel sind sie sinnvoll, wenn nur wenige Datensätze in der "Master"-Tabelle zugehörige Detaildaten besitzen; nur dieses Szenario ist in Deinem Modell nicht vorgesehen - also (aus meiner Sicht) kein Vorteil!
Sie kommen aber eher selten vor. Tatsächlich - und da bin ich wieder auf der Seite von Benjamin - sind die Informationen der "Detailtabelle" ja vollständig vom Schlüssel der Mastertabelle abhängig; und damit wiederum sollten sie Bestandteil der Mastertabelle sein.
1:1-Beziehungen haben wir in der Entwicklung in den 90ern verwendet, um z. B. Personendaten von Gehaltsdaten zu trennen. Letztendlich ging das aber immer zu Lasten der Performance, da ja ein JOIN zwischen Master und Detail vorhanden sein musste. Besser ware ein Konzept, in dem Du keinen Zugriff auf die Tabellen selbst sondern auf Views oder Stored Procedures erteiltst, die wiederum die Sicht auf die Daten einschränken.
MCM - SQL Server 2008
MCSE - SQL Server 2012
db Berater GmbH
SQL Server Blog (german only)- Als Antwort vorgeschlagen Dimitar DenkovMicrosoft contingent staff, Administrator Dienstag, 29. März 2016 06:37
- Als Antwort markiert Aleksander Chalabashiev Dienstag, 5. April 2016 08:58
Alle Antworten
-
Hallo Knorpi,
Du hast Recht - die Fremdschlüsseleinschränkung bezieht sich nur die Detailtabelle aber nicht auf die Mastertabelle. Wie Benjamin bin ich selbst auch immer etwas skeptisch in Bezug auf Trigger. Sie haben einige Nachteile, die halt abgewogen werden müssen (Dabei lasse ich mal die Probleme aus Sicht eines Programmierers aussen vor!):
- Trigger erzeugen temporäre Objekte (INSERTED und DELETED) in TEMPDB
- Trigger sind Bestandteil der Benutzertransaktion und blockieren Ressourcen, die im Trigger angesprochen werden
- Die Verwendung von Triggern führt häufig zu Deadlock-Situationen, wenn im Trigger weitere Objekte angesprochen werden (wie in Deinem Beispiel)
Dein Beispiel als Referenz sieht Punkt 2 der "Bedenken" wie folgt aus:
CREATE TABLE dbo.personal ( idx int NOT NULL PRIMARY KEY CLUSTERED, fname char(50) NOT NULL, lname char(50) NOT NULL ); CREATE TABLE dbo.personal_detail ( personal_idx int NOT NULL PRIMARY KEY CLUSTERED, detail_1 int NOT NULL DEFAULT (0), detail_2 int NOT NULL DEFAULT (0), CONSTRAINT fk_personal FOREIGN KEY (personal_idx) REFERENCES dbo.personal (idx) ON DELETE CASCADE ); GO -- Insert ONE record to avoid system blocked resources -- when locks are investigated INSERT INTO dbo.personal (idx, fname, lname) VALUES (1, 'Uwe', 'Ricken'); GO -- Create the trigger for insertion of data in reference table CREATE TRIGGER dbo.trg_personal_io_insert ON dbo.personal INSTEAD OF INSERT AS SET NOCOUNT ON; -- Insert data from personal INSERT INTO dbo.personal SELECT * FROM inserted; -- Insert data into referencial table INSERT INTO dbo.personal_detail (personal_idx) SELECT i.idx FROM inserted AS i LEFT JOIN dbo.personal_detail AS d ON (i.idx = d.personal_idx) WHERE d.personal_idx IS NULL; SET NOCOUNT OFF; GO
Das obige Beispiel habe ich aus Deinen Anforderungen entnommen und den entsprechenden Trigger (hätte auch ein INSERT-Trigger sein können) implementiert. Nun wird ein weiterer Datensatz eingetragen. Dabei wird aber dieser Vorgang in einer expliziten Transaktion ausgeführt. Ich lasse die Transaktion zunächst geöffnet!
BEGIN TRANSACTION insertdata; GO INSERT INTO dbo.personal (idx, fname, lname) VALUES (2, 'Beate', 'Ricken'); SELECT resource_type, CASE WHEN resource_type = N'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id) ELSE CAST(resource_associated_entity_id AS nvarchar(255)) END AS resource_type, request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID;
Der erste Teil der Abfrage fügt in die Tabelle dbo.personal einen neuen Datensatz ein. Im zweiten Statement überprüfe ich, welche Ressourcen durch die Benutzertransaktion blockiert sind.
An der Abbildung kannst Du sehr schön erkennen, dass Ressourcen beider Tabellen blockiert werden. Solange die Transaktion nicht abgeschlossen ist, kann keine der beiden Ressourcen freigegeben werden und andere Prozesse müssen warten.
1:1-Beziehungen machen - entgegen der Meinung von Benjamin - in manchen Szenarien sicherlich Sinn. Zum Beispiel sind sie sinnvoll, wenn nur wenige Datensätze in der "Master"-Tabelle zugehörige Detaildaten besitzen; nur dieses Szenario ist in Deinem Modell nicht vorgesehen - also (aus meiner Sicht) kein Vorteil!
Sie kommen aber eher selten vor. Tatsächlich - und da bin ich wieder auf der Seite von Benjamin - sind die Informationen der "Detailtabelle" ja vollständig vom Schlüssel der Mastertabelle abhängig; und damit wiederum sollten sie Bestandteil der Mastertabelle sein.
1:1-Beziehungen haben wir in der Entwicklung in den 90ern verwendet, um z. B. Personendaten von Gehaltsdaten zu trennen. Letztendlich ging das aber immer zu Lasten der Performance, da ja ein JOIN zwischen Master und Detail vorhanden sein musste. Besser ware ein Konzept, in dem Du keinen Zugriff auf die Tabellen selbst sondern auf Views oder Stored Procedures erteiltst, die wiederum die Sicht auf die Daten einschränken.
MCM - SQL Server 2008
MCSE - SQL Server 2012
db Berater GmbH
SQL Server Blog (german only)- Als Antwort vorgeschlagen Dimitar DenkovMicrosoft contingent staff, Administrator Dienstag, 29. März 2016 06:37
- Als Antwort markiert Aleksander Chalabashiev Dienstag, 5. April 2016 08:58
