local uniqueness of a column

Hi,
I'm dealing with a database that is used to contain files for multiple
client companies, in one table I would like to enforce the uniqueness of the
file name column within a broker (that is file name must be unique within
each company). Currently I reinforce this with a trigger on update and
insert event and count for the duplicates within the broker, it causes lots
of deadlocks because it has to scan and lock up too many rows (I have to
stick with the Repeat Read Isolation level.) I'm pretty sure that it's not
the only cause of deadlock but it should be one of them. Has anybody known
how to resolve this or have any comment advice that can share with me?
thanks!!Zeng,
It would help to see your CREATE TABLE statement, but it sounds like
you want (broker, file_name) to be unique. If the broker is also a column
in your table, just declare a UNIQUE constraint, as in
create table AllFiles (
broker_id int not null references brokers(id),
file_name nvarchar(200),
other_stuff varchar(80),
unique (broker_id, file_name)
)
or to add this constraint to the existing table:
alter table AllFiles
add constraint AllFiles_broker_file unique(broker_id, file_name)
If this isn't what you want, you'll have to post the CREATE TABLE
statement and explain what "within a broker" or "within each company"
means.
Steve Kass
Drew University
Zeng wrote:
>Hi,
>I'm dealing with a database that is used to contain files for multiple
>client companies, in one table I would like to enforce the uniqueness of the
>file name column within a broker (that is file name must be unique within
>each company). Currently I reinforce this with a trigger on update and
>insert event and count for the duplicates within the broker, it causes lots
>of deadlocks because it has to scan and lock up too many rows (I have to
>stick with the Repeat Read Isolation level.) I'm pretty sure that it's not
>the only cause of deadlock but it should be one of them. Has anybody known
>how to resolve this or have any comment advice that can share with me?
>thanks!!
>
>
>|||It's a good idea, I just didn't think of it as a unique compound keys.
However, wouldn't that have the same in term of performance and deadlock
chance? In another word, wouldn't sqlserver have to do the similar scanning
to reenforce the constraint?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uVod32I6EHA.2512@.TK2MSFTNGP09.phx.gbl...
> Zeng,
> It would help to see your CREATE TABLE statement, but it sounds like
> you want (broker, file_name) to be unique. If the broker is also a column
> in your table, just declare a UNIQUE constraint, as in
> create table AllFiles (
> broker_id int not null references brokers(id),
> file_name nvarchar(200),
> other_stuff varchar(80),
> unique (broker_id, file_name)
> )
> or to add this constraint to the existing table:
> alter table AllFiles
> add constraint AllFiles_broker_file unique(broker_id, file_name)
> If this isn't what you want, you'll have to post the CREATE TABLE
> statement and explain what "within a broker" or "within each company"
> means.
> Steve Kass
> Drew University
>
> Zeng wrote:
> >Hi,
> >
> >I'm dealing with a database that is used to contain files for multiple
> >client companies, in one table I would like to enforce the uniqueness of
the
> >file name column within a broker (that is file name must be unique within
> >each company). Currently I reinforce this with a trigger on update and
> >insert event and count for the duplicates within the broker, it causes
lots
> >of deadlocks because it has to scan and lock up too many rows (I have to
> >stick with the Repeat Read Isolation level.) I'm pretty sure that it's
not
> >the only cause of deadlock but it should be one of them. Has anybody
known
> >how to resolve this or have any comment advice that can share with me?
> >thanks!!
> >
> >
> >
> >
> >|||On Wed, 22 Dec 2004 18:40:49 -0800, "Zeng" <zzy@.nonospam.com> wrote:
>It's a good idea, I just didn't think of it as a unique compound keys.
>However, wouldn't that have the same in term of performance and deadlock
>chance? In another word, wouldn't sqlserver have to do the similar scanning
>to reenforce the constraint?
It creates an index. It's very efficient. Try it!
J.|||As J. pointed out, when you put a UNIQUE constraint on a table, an index
is created to support the constraint. Row locks can be used once
there's an index.
SK
Zeng wrote:
>It's a good idea, I just didn't think of it as a unique compound keys.
>However, wouldn't that have the same in term of performance and deadlock
>chance? In another word, wouldn't sqlserver have to do the similar scanning
>to reenforce the constraint?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uVod32I6EHA.2512@.TK2MSFTNGP09.phx.gbl...
>
>>Zeng,
>> It would help to see your CREATE TABLE statement, but it sounds like
>>you want (broker, file_name) to be unique. If the broker is also a column
>>in your table, just declare a UNIQUE constraint, as in
>>create table AllFiles (
>> broker_id int not null references brokers(id),
>> file_name nvarchar(200),
>> other_stuff varchar(80),
>> unique (broker_id, file_name)
>>)
>>or to add this constraint to the existing table:
>>alter table AllFiles
>> add constraint AllFiles_broker_file unique(broker_id, file_name)
>>If this isn't what you want, you'll have to post the CREATE TABLE
>>statement and explain what "within a broker" or "within each company"
>>means.
>>Steve Kass
>>Drew University
>>
>>Zeng wrote:
>>
>>Hi,
>>I'm dealing with a database that is used to contain files for multiple
>>client companies, in one table I would like to enforce the uniqueness of
>>
>the
>
>>file name column within a broker (that is file name must be unique within
>>each company). Currently I reinforce this with a trigger on update and
>>insert event and count for the duplicates within the broker, it causes
>>
>lots
>
>>of deadlocks because it has to scan and lock up too many rows (I have to
>>stick with the Repeat Read Isolation level.) I'm pretty sure that it's
>>
>not
>
>>the only cause of deadlock but it should be one of them. Has anybody
>>
>known
>
>>how to resolve this or have any comment advice that can share with me?
>>thanks!!
>>
>>
>>
>
>