How to force unique entries in a linking table?

I have a table 'Group2Operation' that stores many to many relations
between the 'Group' table and the 'Operation' table (each group is has
permission to perform one or more of the available operations)

PROBLEM
=======
I need to prevent duplicate entries being created. e.g. lets say that
in the 'Group2Operation' table a record links the 'editor' group to
the 'publish' operation. Should I prevent an administrator creating a
duplicate of that record? (Otherwise deleting that permission will
have to be done twice or more for it to be effective)

SOLUTION?
=========
So far I've done this with a trigger:

CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
FOR INSERT, UPDATE
AS UPDATE Group2Operation
SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag

The 'NoDuplicate' unique index column in the Group2Operation table
stores a concatenation of the unique group and operation identifiers.
So when an attempt is made to create a record, the trigger is fired.
If there is a duplicate, this will mean a duplicate entry in the
'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
the duplication will be prevented.

WHAT DO YOU THINK?
==================
What do you think? Am I going about this in the right way? Is a
trigger a good way to do this or should I rely on application logic to
prevent duplicates?

Any help appreciated by this db novice.
John GristConsider creating a PRIMARY KEY or UNIQUE constraint on the Group2Operation
table. This will ensure duplicate entries cannot be inserted. There is no
need to concatenate values or use a separate table since you can specify a
composite key:

ALTER TABLE Group2Operation
ADD CONSTRAINT PK_Group2Operation
PRIMARY KEY (GroupID, OperationTag)

BTW, it's a good practice to post DDL (create table) when posting questions
to this forum.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"grist2mill" <grist2mill@.excite.com> wrote in message
news:46e240e.0409030059.79f2f36d@.posting.google.co m...
>I have a table 'Group2Operation' that stores many to many relations
> between the 'Group' table and the 'Operation' table (each group is has
> permission to perform one or more of the available operations)
> PROBLEM
> =======
> I need to prevent duplicate entries being created. e.g. lets say that
> in the 'Group2Operation' table a record links the 'editor' group to
> the 'publish' operation. Should I prevent an administrator creating a
> duplicate of that record? (Otherwise deleting that permission will
> have to be done twice or more for it to be effective)
> SOLUTION?
> =========
> So far I've done this with a trigger:
> CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
> FOR INSERT, UPDATE
> AS UPDATE Group2Operation
> SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
> The 'NoDuplicate' unique index column in the Group2Operation table
> stores a concatenation of the unique group and operation identifiers.
> So when an attempt is made to create a record, the trigger is fired.
> If there is a duplicate, this will mean a duplicate entry in the
> 'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
> the duplication will be prevented.
> WHAT DO YOU THINK?
> ==================
> What do you think? Am I going about this in the right way? Is a
> trigger a good way to do this or should I rely on application logic to
> prevent duplicates?
> Any help appreciated by this db novice.
> John Grist|||That looks much better. I though there ought to be a better way.
Many thanks for your help
John

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<uh_Zc.14996$lu3.12768@.newssvr24.news.prodigy.com>...
> Consider creating a PRIMARY KEY or UNIQUE constraint on the Group2Operation
> table. This will ensure duplicate entries cannot be inserted. There is no
> need to concatenate values or use a separate table since you can specify a
> composite key:
> ALTER TABLE Group2Operation
> ADD CONSTRAINT PK_Group2Operation
> PRIMARY KEY (GroupID, OperationTag)
> BTW, it's a good practice to post DDL (create table) when posting questions
> to this forum.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "grist2mill" <grist2mill@.excite.com> wrote in message
> news:46e240e.0409030059.79f2f36d@.posting.google.co m...
> >I have a table 'Group2Operation' that stores many to many relations
> > between the 'Group' table and the 'Operation' table (each group is has
> > permission to perform one or more of the available operations)
> > PROBLEM
> > =======
> > I need to prevent duplicate entries being created. e.g. lets say that
> > in the 'Group2Operation' table a record links the 'editor' group to
> > the 'publish' operation. Should I prevent an administrator creating a
> > duplicate of that record? (Otherwise deleting that permission will
> > have to be done twice or more for it to be effective)
> > SOLUTION?
> > =========
> > So far I've done this with a trigger:
> > CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
> > FOR INSERT, UPDATE
> > AS UPDATE Group2Operation
> > SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
> > The 'NoDuplicate' unique index column in the Group2Operation table
> > stores a concatenation of the unique group and operation identifiers.
> > So when an attempt is made to create a record, the trigger is fired.
> > If there is a duplicate, this will mean a duplicate entry in the
> > 'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
> > the duplication will be prevented.
> > WHAT DO YOU THINK?
> > ==================
> > What do you think? Am I going about this in the right way? Is a
> > trigger a good way to do this or should I rely on application logic to
> > prevent duplicates?
> > Any help appreciated by this db novice.
> > John Grist