sql server programming: Hi guys,

Below is two related tables. If I need to delete a lot of records from the
child table (EmailGroupMembers) but the deletion must not result in records
on its own (without a EmailGroup member that is..) in the parent table
(EmailAddresses), how should I think? Can I achieve that without having to
run each email to be removed through a SP?

Thanks for any help.

Regards,
Jonah Olsson


CREATE TABLE [EmailAddresses] (
[email_guid] [varchar] (40) NOT NULL ,
[email] [nvarchar] (255) NOT NULL
CONSTRAINT [PK__EmailAddresses__39237A9A] PRIMARY KEY CLUSTERED
(
[email_guid]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [EmailGroupMembers] (
[group_id] [int] NOT NULL ,
[email_guid] [varchar] (40) NOT NULL ,
CONSTRAINT [EmailAddresses_EmailGroupMembers_FK1] FOREIGN KEY
(
[email_guid]
) REFERENCES [EmailAddresses] (
[email_guid]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

Jonah,

There is not a constraint that will do this. You could create a trigger
(either an INSTEAD OF or an AFTER) that would do the following.

An INSTEAD OF would be better since it would require less work.

1. Count how many rows in inserted for each email_guid.
2. Count how many rows in EmailGroupMembers for each email_guid in inserted.
3. Whenever the number in inserted and in EmailGroupMembers is the same for
each email_guid you can either:
a) Reject the deletion
b) Programmatically determine that one of the EmailGroupMembers is
preserved and delete the rest.

If you are on an earlier version of SQL Server and only have AFTER triggers,
then option 3.b would require you to reinsert a row into EmailGroupMembers
from inserted.

Russell Fields


[quoted text, click to view]

Russel,
Thanks a lot! Worked like a charm.

/Jonah


"Russell Fields" <RussellFields@NoMailPlease.Com> skrev i meddelandet
news:uWlHeRptDHA.540@tk2msftngp13.phx.gbl...
[quoted text, click to view]

[quoted text, click to view]
[sic] from the child table [sic] (EmailGroupMembers) but the deletion
must not result in records [sic] on its own (without a EmailGroup member
that is..) in the parent table (EmailAddresses), how should I think? <<

Rows are not records; totally different concept. SQL has referenced and
referencing tables; hieratchical DBMS systems like IMS, TOTAL, etc. do
have child and parent records. Learn the terminolgy and thinking in SQL
will come much easier.

You used NVARCHAR(n) for an email address; how many email addresses have
you seen that use anything other than Latin-1 characters? If you don't
pick the right datatype, you will see Chinese inthat column. And you do
know that GUID is not a key by definition.

Some specs were not given. Can an email exist without being in a group?
Would it make sense to have a group zero if that is the case?

--if a person can be in more than one group
CREATE TABLE EmailGroups
(group_id INTEGER NOT NULL,
email VARCHAR (255) NOT NULL,
PRIMARY KEY (group_id, email));

--if a person can be in only one group
CREATE TABLE EmailGroups
(group_id INTEGER NOT NULL,
email VARCHAR (255) NOT NULL PRIMARY KEY);

Of course you need CHECK() constriants for valid email addresses, valid
group_id, etc. But your problem has disappeared with a simple change in
design.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***