Char and Varchar: duplicates

Showing posts with label duplicates. Show all posts

Monday, March 19, 2012

Check for Duplicates Help

I have a table that has an ssn column that is nullable. I want to allow duplicate null values but if someone tries to insert or update th column with a value that is not null I need to check to see if the value already exists and if so generate an error.

Can anyone tell or better yet provide an example of the best way to do this. I'm guessing a trigger but I'm pretty green when it comes to writing efficient triggers.

Thanx in advance!!!You can not use unique index but trigger will be good for your case:

drop table test
go
create table test(ssn varchar(7))
go
alter trigger no_duplicates on test
for insert,update
as
if exists(select ssn from test
where ssn in(select ssn from inserted)
and ssn is not null
group by ssn having count(*)>1)begin
raiserror('duplicates!!!',0,1)
rollback tran
end|||Thanks a bunch snail!!!|||No kidding?

DB2 has something SQL Server doesn't?

UNIQUE WHERE NOT NULL

Nothing like that here?

I'm shocked!|||that's an extension for fat ibm dba's

Check for duplicates

I am trying to do a check if the record already exists in my database. I wrote this code and all is well except it takes about 2-5 seconds to execute on my access database. Wen you know this one is called about 30 000 to 50 000 times that is unacceptable.

Can someone please take a look and see if this can be accelerated?

Thanks

Napivo

Public Function CheckDouble(psWeb As String, psIP As String, pdDate As Date, _
pdTime As Date, psEnvironment As String, psControler As String, _
plType As Long, plSize As Long, pbSpecial As Boolean, plSpend As Long) As Boolean

Dim rs As ADODB.Recordset
Dim sql As String

sql = "select count(ip) as cnt from Logs where [IP] = '" & psIP & "'" _
& " And [Web] = '" & psWeb & "'" _
& " And [Date] = #" & Format(pdDate, "MM/DD/YY") & "#" _
& " and [Time] = #" & Format(pdTime, "HH:MM") & "#" _
& " and [Environment] = '" & psEnvironment & "'" _
& " and [Controler] = '" & psControler & "'" _
& " and [Spend] =" & plSpend & "" _
& " and [Type] =" & plType & "" _
& " and [Special] = " & CBool(pbSpecial) & "" _
& " and [Size] =" & plSize

On Error GoTo CheckDouble_Error
Set rs = oCon.Execute(sql)
On Error GoTo 0
If rs.Fields("cnt").Value > 0 Then
CheckDouble = True
End If
Exit Function
CheckDouble_Error:

Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure CheckDouble of Class Module cLogDatabase"
End Function

This is an example of the SQL statement I get

select count(ip) as cnt from Logs where [IP] = '194.235.127.40 ' And [Web] = 'WEB2' And [Date] = #07/01/04# and [Time] = #14:14# and [Environment] = 'AON' and [Controler] = 'EAFormController' and [Spend] =2 and [Type] =200 and [Special] = False and [Size] =23489My first suggestion would be to look at adding indices to make it easier for Jet to process the query. You may well need to experiment a bit to find a good combination of columns, since Jet often makes "interesting" choices where indicies are concerned.

If that doesn't help enough, I'd switch to MSDE in order to get more help understanding the query itself. It is a lot easier to find and fix query problems in MSDE than it is in Jet, and once you've solved the problem you can almost always move back to Jet if you want.

-PatP

Check duplicates when inserting

hi all,
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another process
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
SoHi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
> Thx for your reply.
> But i want to check existing record with inserting record by columns of that
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
> > Hi
> > Itzik Ben-Gan has written a greate example
> >
> > CREATE TABLE #Demo (
> > idNo int identity(1,1),
> > colA int,
> > colB int
> > )
> >
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (2,4)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (4,2)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (5,1)
> > INSERT INTO #Demo(colA,colB) VALUES (8,1)
> >
> > PRINT 'Table'
> > SELECT * FROM #Demo
> >
> > PRINT 'Duplicates in Table'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo <> B.idNo
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Duplicates to Delete'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > DELETE FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Cleaned-up Table'
> > SELECT * FROM #Demo
> >
> > DROP TABLE #Demo
> >
> > "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> > news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > > hi all,
> > >
> > > I am confuse now,Let me explain the process.
> > >
> > > In database table called "Individual " it's store all customer information
> > > user can insert in this table by .NET application but we have another
> > process
> > > that can insert individual from file.
> > >
> > > Sometimes same individual repeat again so before i insert into Individual
> > > table i have to check that it's exist or not but there is not any unique
> > ID
> > > for each customer so basically one customer in individual table have
> > > Indentity generated IndividualID so when i try to insert there is no way
> > to
> > > find out that is this same customer or diffrent.
> > >
> > > Indivivual table have another column like
> > > FirstName
> > > LastName
> > > BirthDay
> > > BirthMonth
> > > BirthYear
> > > Gender
> > > Maritial Status
> > > Street
> > > StreetName
> > > AddressLine1
> > > City
> > > PostalCode
> > >
> > > So how can i find that this is duplicate or not.
> > >
> > > I can actually match this column value with inserting individual value but
> > > you know coustomer can registered with little diffrent with names or
> > address
> > > like ("daniel" can register with second time with "dan")
> > >
> > > So is there is any trick to compare this duplicates.
> > >
> > > Thanks in advance
> > >
> > >
> > > So
> >
> >
> >

Sunday, March 11, 2012

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-Paul
CHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegro ups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:

>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegroups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:
>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:
>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,

I need to enforce that a table does not have "duplicates" for a
specific status type in the table.

If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.

I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.

Just when the status = 2, there can not be any other rows with the same
ID and status = 2.

Any ideas?

-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/

<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegro ups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul

Hi Paul,

Apart from the trigger Tibor suggests, there are two other options:

1. Use an indexed view:

CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go

2. Use a computed column (assuming PKCol is the primary key):

ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go

(both versions untested - bewarer of typos!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger inste
ad.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegroups.
com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||You can also create a view for STATUS = 2 and create a unique clustered inde
x
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:

>You can also create a view for STATUS = 2 and create a unique clustered ind
ex
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Monday, March 19, 2012

Check for Duplicates Help

Check for duplicates

Check duplicates when inserting

Check duplicates when inserting

Check duplicates when inserting

Sunday, March 11, 2012

CHECK Constraint to prevent a conditional duplicate

CHECK Constraint to prevent a conditional duplicate

CHECK Constraint to prevent a conditional duplicate

CHECK Constraint to prevent a conditional duplicate

Char and Varchar

Blog Archive

About Me