Datenbankdesign n : m Beziehung Referentielle Integrität

[Skorpion81]

Hallo,
ich versuche gerade eine Datenbank zu erstellen.

Es gibt eine Tabelle in der stehen alle Anwender drin.

CREATE TABLE [dbo].[users1](
[userkey] [int] NOT NULL,
[sitekey] [int] NOT NULL,
[userlastname] [nchar](35) COLLATE Latin1_General_CI_AS NULL,
[userfirstname] [nchar](30) COLLATE Latin1_General_CI_AS NULL,
[password] [nchar](16) COLLATE Latin1_General_CI_AS NULL,
[username] [nchar](35) COLLATE Latin1_General_CI_AS NULL, Primary Key ( userkey, sitekey)
)

Der Datensatz wird durch den Verknüpften Primärschlüssel "userkey, sitekey" eindeutig.

Dann gibt es eine Tabelle in der gespeichert werden soll, wer darf welche User sehen.
Der Anwender meldet sich an einer Applikation an, die ihm ermöglicht, den momentanen Status des anderen Anwender anzuzeigen, z.B. der telefoniert, der macht pause, er macht Ablage... usw. Aber es soll administriert werden, wer darf wen sehen.
Der Chef will evtl. nicht von seinen Mitarbeitern "beobachtet" werden, aber die Mitarbeiter untereinander sollen sehen was die Kollegen machen und sie sollen auch ihren eigenen Status dargestellt bekommen.
Hier mal ein Versuch von mir für die Tabelle.

Hier mal mein
CREATE TABLE [dbo].[userallowedagents1](
[userkey] [int] NOT NULL,
[sitekey] [int] NOT NULL,
[autuserkey] [int] NOT NULL,
[entitysitekey] [int] NOT NULL,
Primary Key (userkey, sitekey, autuserkey, entitysitekey),
Foreign key (userkey, sitekey) references users1 (userkey, sitekey)
On Delete Cascade);

Wenn dann z.B. ein User in der Tabelle "users1" gelöscht wird sollen auch alle damit zusammenhängende Datensätze in der Tabelle "userallowedagents1" gelöscht werden.

So wie ich jetzt den Foreign Key gesetzt habe, werden aber nur die Datensätze gelöscht, welche der Anwender gesehen hat.

Also hier mal ein Beispiel
user1
1,111,Nachname1, Vorname1, Password1,Username1
2,111,Nachname2, Vorname2, Password2, Username2
1,123,Nachname1_123, Vorname1_123, Password1_123, Username1_123

userallowedagents
1,111,1,111
1,111,2,111
1,111,1,123
2,111,1,111
2,111,2,111
2,111,1,123
1,123,1,111
1,123,2,111
1,123,1,123

Wenn ich nun in der users Tabell den User 1,111 lösche, dann sieht die Tabelle userallowedagents wie folgt aus:

2,111,1,111
2,111,2,111
2,111,1,123
1,123,1,111
1,123,2,111
1,123,1,123


Aber ich hätte gerne folgendes:

2,111,2,111
2,111,1,123
1,123,2,111
1,123,1,123

Weil die Anwender 2,111 und 1,123 sollen den Anwender 1,111 ja nun auch nicht mehr "überwachen" können, da der vielleicht aus dem Unternehmen ausgeschieden ist.

Kann mir da einer helfen und mir sagen, wie ich da am geschicktesten die Schlüssel usw. definieren muß?

Vielen Dank und Grüße,
Skorpion81

[Falk Prüfer]

Hallo Skorpion81,

Dein Tabellenlayout würde ich von vornherein als "ungünstig" betrachten - wenn ich das mal so sagen darf

Gerade für dein Problem ist ein verketteter Primärschlüssel mehr als ungünstig.
Mein Vorschlag:

CREATE TABLE [dbo].[users1](
[userpk] [int] NOT NULL,
[userkey] [int] NOT NULL,
[sitekey] [int] NOT NULL,
[userlastname] [nchar](35) COLLATE Latin1_General_CI_AS NULL,
[userfirstname] [nchar](30) COLLATE Latin1_General_CI_AS NULL,
[password] [nchar](16) COLLATE Latin1_General_CI_AS NULL,
[username] [nchar](35) COLLATE Latin1_General_CI_AS NULL, 
Primary Key ( userpk)
)

Anm.: Auf userkey, sitekey kannst du ja zusätzlich noch einen UNIQUE-Key legen.

CREATE TABLE [dbo].[userallowedagents1](
[userallowedagentspk] [int] NOT NULL,
[userid] [int] NOT NULL,
[autuserid] [int] NOT NULL,
Primary Key (userallowedagentspk),
Foreign key (userid) references users1 (userpk)
  On Delete Cascade,
Foreign key (autuserid) references users1 (userpk)
  On Delete Cascade);

Jetzt sollte das kaskadierende Löschen wie gewünscht funktionieren.

Gruß Falk

[Skorpion81]

HI Falk,
leider funktioniert das nicht. Ich kann wohl nicht zwei Foreign Keys mit On Delete Cascade durchführen.
siehe folgende Fehlermeldung... oder habe ich irgendwie was falsch bei copy & paste gemacht :-)


Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK__userallow__autus__01142BA1' on table 'userallowedagents1' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


Trotzdem vielen Dank für den Hinweis mit den Primärschlüsseln.

Hattest du das bei dir mal ausprobiert? Ich verwende MSSQL 2005

Grüße,
Skorpion81

[Falk Prüfer]

Hallo Skorpion81,

wie und ob der MSSQL 2005 mehrere Foreign Key mit ON ... CASCADE untertsützt entzieht sich meiner Kenntnis. Um ehrlich zu sein habe ich das auch noch nie ausprobiert
Alternativ kannst du die Foreign Key ja "normal" anlegen und die kaskadierende Löschung über einen BEFORE DELETE Trigger realisieren.

Gruß Falk

[Skorpion81]

Hi Falk,
ja, das mit dem Trigger habe ich mir auch schon überlegt.
Schade das es nicht über die Foreign Keys geht, die Lösung fände ich besser.
Vielen Dank für deine Unterstützung.