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

How to find a perticular row?

Hi all,
I want to select last but one row in a table. I shouldn't use pk_id of the table ,because the entries in a table are jumbled.
I want the last but one row that as entered.
Thanks in advance
venkatHi vencat

This gets right to the nub of relational theory. Tables in an RDBMS are sets of data. Think of a Venn diagram - there is no order right? Just some "clouds" to represent sets. As such there is no such thing as last or first or last but one in a relational table. Order of insertion and physical order on disk is irrelevent. So - unless you have used some sort of timestamp for when you inserted the data (say a column with a default value of GETDATE()) or some other method of determining a logical order of the data then you cannot satisfy your requirement

HTH|||select top 1 * from yourtable order by newid()

;)|||select top 1 * from yourtable order by newid()

;)

doesn't that just grab a random row?|||Originally Posted by jezemine
select top 1 * from yourtable order by newid()
this is definitely not a correct way to select last but one row. I don't think it is not possbile if you don't have timestamp or identity or smiliar column.|||doesn't that just grab a random row?
this is definitely not a correct way to select last but one row. I don't think it is not possbile if you don't have timestamp or identity or smiliar column.Did you guys miss the wink? And Jesse's increasing ratio of sarcy to serious posts? ;)

Anyway - everyone knows it is:
select top 1 * from yourtable order by newid() DESC|||select top 1 *
from ( select t.*, newid() as random_number
from yourtable as t ) as inline_view
order by random_number desc|||SELECT TOP 1 A_SQL99_compliant_derived_table.*
FROM --A SQL99 compliant derived table
(SELECT *
, rn = row_number() OVER (ORDER BY newid() DESC)
FROM meTable) AS A_SQL99_compliant_derived_table
WHERE rn = 1
My - we are a witty bunch.

I said witty :)|||very nice, and very elegant, too

but shouldn't it be An_SQL99_compliant_derived_table|||very nice, and very elegant, tooThanks :cool: I like to write tight, elegent code to meet the requirements.

but shouldn't it be An_SQL99_compliant_derived_tableI'm an old skooler Rudy - it is pronounced SEQUEL in the flump household.:)|||ok, ok, i feel bad now. ;)

this will give you the second to last row, where "last" is determined by ordering the "item" column alphabetically.

declare @.t table (item varchar(10))

insert into @.t
select 'aaa' union all
select 'bbb' union all
select 'ccc' union all
select 'ddd' union all
select 'eee' union all
select 'fff'

select top 1 a.* from
(select top 2 * from @.t order by item desc) a
order by item asc|||select top 1 * from yourtable order by newid()

;)

-It is retrieving only row 1 based on the primary key of the table.I am not willing to use primary key.

Thanks|||Ok..Guys I tested all your posts.but no one retrieving the last but row.But some of them returning last row depending on primary key of the table ,that i can achieve easily .but i don't want to use primary key...

when i "select * " it is retrieving always a some order (but this order is same for all times) and where the rows are jumbled.i.e showing in an order they were entered.

I want that last but one row showing in "select * from mytable" result set..

Thanks for great help..!!|||ok, ok, i feel bad now. ;)

this will give you the second to last row, where "last" is determined by ordering the "item" column alphabetically.

declare @.t table (item varchar(10))

insert into @.t
select 'aaa' union all
select 'bbb' union all
select 'ccc' union all
select 'ddd' union all
select 'eee' union all
select 'fff'

select top 1 a.* from
(select top 2 * from @.t order by item desc) a
order by item asc

Your query is working fine...i am getting what i want..But i can create a table variable for a existing table..and how can run your query on a existing table without insertion of new data?

Thanks|||it's just an example. do you understand what the example is doing?

just alter the query to target your table instead of @.t, and alter "item" to whatever column you want to order by.|||-It is retrieving only row 1 based on the primary key of the table.I am not willing to use primary key.

Thanks

that was meant to be a little joke. you probably don't know much sql if you thought that might give you what you wanted. :)

you might want to spend some time learning SQL better.|||when i "select * " it is retrieving always a some order (but this order is same for all times) no it isn't

it only appears to be the same order every time

it might even actually be exactly the same order, many times in a row

but without an ORDER BY clause, there is no guarantee of any order in the results