Constraint Question 2

[sonya9879]

Hi,

I have a table with the following columns:

uniqueid
customerid
destxt

I want to add a constraint that doesn't allow duplicate values in the destxt column for each customer ID. So two customers could have the same destxt value but not the same customer.

example:

10 12345 Hello
11 12345 Test
12 12346 Hello
13 12345 Hello ->>> ERROR Duplicate values for the same customer ID should not be allowed.

Any ideas? I knew how to do this in Oracle but I forgot, I am getting old .

Many thanks to all.

 

[kxramse]

You should ask this in the Oracle forum. You just have to set up your table with a key that doesn't allow duplicates on the columns mentioned. I'd walk you through it but I don't have the user interface for Oracle here right now.

 

[sonya9879]

Hi kxramse, thanks for your reply but I am not using oracle, I am using SQL Server 2005 that is why I posted this here.

 

[bborissov]

Create UNIQUE index by both fields:

CREATE UNIQUE NONCLUSTERED INDEX IX_SOMENAME
 ON dbo.YourTable
	(
	 customerID,
         MessageText
	) ON [PRIMARY]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

here you go, change the table name and the constraint name to soemthing that you want

ALTER TABLE TableName ADD CONSTRAINT
	IX_UniqueConst UNIQUE NONCLUSTERED 
	(
	customerid,
	destxt
	) ON [PRIMARY]

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[sonya9879]

What can I say? bborissov and SQLDenis, you are both a star thanks so much.

 

[kxramse]

I might have solved it if you didn't say Oracle. ;-) But who am I kidding, these other guys know SQL Server inside out and its great having them here.