Check Duplicate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Check Duplicate

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2010-03-09 : 23:58:28

Hello,

I had a table name: candidate_details
Columns under this table are: ID(primary key), name, dob, mobile, telephone & email

Now I want to check the duplicate entries, but my duplicate checking should be with name, dob & email.

If either of the two columns found similar with the existing data in table, TRIGGER should RAISERROR.

PLEASE HELP ME.

Daipayan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-10 : 04:23:47

why not just make a unique constraint over the three columns?

then you wouldn't need a trigger.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

daipayan
Posting Yak Master

181 Posts

Posted - 2010-03-10 : 04:41:22

CAN unique constraint validate by checking either of the two columns among the three????
Am giving a SAMPLE TABLE also:
TABLE:


CREATE TABLE [candidate_details] (
        [candidate_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
        [name] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [dob] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [mobile] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [email] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        CONSTRAINT [PK_candidate_details] PRIMARY KEY  CLUSTERED 
        (
                [candidate_ID]
        )  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

TEST DATA:


INSERT INTO [dbo].[candidate_details]([name], [dob], [mobile], [email])
VALUES('Alex', '03/26/1970', '9876567898', 'alex@example.com')
VALUES('John', '12/24/1987', '9876567896', 'john@gmail.com')
VALUES('Sinha', '06/06/1980', '9877297783', 'sinha@yahoo.com')
VALUES('Alex', '09/21/1980', '9836829829', 'alex@example.com')

Here you will see, there are 2 candidate having same name n same E-Mail ID but different DOB. This can be accepted. But if the same candidate having same name, e-mail & DOB, that should not be accepted. A duplication should be checked with either of the two fields i.e. Name & E-Mail, E-Mail & DOB, Name & DOB or individual Name, E-mail, DOB, BUT IF ALL THE THREE CRITERIA ARE MATCHING, DATA SHOULD NOT BE INSERTED.

Daipayan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-10 : 05:05:03

well what about adding these constraints?


		, CONSTRAINT [UNQ_mobile_email] UNIQUE (
			[mobile]
			, [email]
			)

		, CONSTRAINT [UNQ_dob_email] UNIQUE (
			[dob]
			, [email]
			)

		, CONSTRAINT [UNQ_dob_mobile] UNIQUE (
			[dob]
			, [mobile]
			)

		, CONSTRAINT [UNQ_dob_mobile_email] UNIQUE (
			[dob]
			, [mobile]
			, [email]
			)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajshekar82
Starting Member

1 Post

Posted - 2010-03-10 : 05:22:32

Hi,

Yes i believe adding a unique constraint should fix the problem

code: " Alter table candidate_details add Constraint unq_name_dob_email Unique (name, dob, email)"

and try inserting same values, should give you an error

hope this solves the problem

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-10 : 05:24:26

quote:
Originally posted by rajshekar82

Hi,

Yes i believe adding a unique constraint should fix the problem

code: " Alter table candidate_details add Constraint unq_name_dob_email Unique (name, dob, email)"

and try inserting same values, should give you an error

hope this solves the problem

Requirement was : If either of the two columns found similar with the existing data in table,

I think you'll need the 4 unique constraints in that case -- probably still much better idea than a trigger though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

daipayan
Posting Yak Master

181 Posts

Posted - 2010-03-10 : 05:31:40

I'll try today and get back to you as soon as possible.

Thank You for your valuable suggestions.

Daipayan

revolution
Starting Member

1 Post

Posted - 2011-10-10 : 23:19:12

I have a same question also. what should i do if this is my query

i need to put a unique ID in this table but in our system every time i canceled the transaction it duplicates the same ID,but there are table canceled with Y and N, ineed to do is to allow duplication if the table have a Y input in canceled table, and don't allow duplication in N input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 02:07:45

quote:
Originally posted by revolution

I have a same question also. what should i do if this is my query

i need to put a unique ID in this table but in our system every time i canceled the transaction it duplicates the same ID,but there are table canceled with Y and N, ineed to do is to allow duplication if the table have a Y input in canceled table, and don't allow duplication in N input.

sounds like what you need is check constraint based on udf.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources