How to force unique entries in a linking table?

[grist2mill]

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

[Dan Guzman]

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" <gr********@excite.com> wrote in message
news:46*************************@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

[grist2mill]

That looks much better. I though there ought to be a better way.
Many thanks for your help
John

"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message news:<uh*******************@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" <gr********@excite.com> wrote in message
news:46*************************@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