Please consider the following table:

create table mytable(
pkid int,
d1 int
c1 int null,
c2 int null,
c3 int null
)

I want to make sure that for a given value of d1: c1, c2, and c3 are unique.
How do I create unique constraints for that. I went throught this forum and
found some examples where another column was created and set to primary key
if any of the column was null. I was not sure if this was possible here.

Please let me know if unique consraints are possible? if not what else could
I do.

Thank you in advance and please let me know if this needs more
clarification...

not sure what do you need.
is this set of data acceptable?

insert into mytable(1,1,1,2,3)
insert into mytable(2,1,2,3,1)

what about this:

insert into mytable(3,2,1,1,1)

what about this:

insert into mytable(4,3,null,null,null)

Alexander, how would I set up the constraints though??

TIA..

Show quoteHide quote

I forgot to mention that c1,c2,and c3 could be all null. This kind of data
should be allowed. How would I setup unique constraints on d1,c1,c2,c3 such
that all 3 nulls are allowed in c1,c2,and c3

Allowed data:

1,1,2,3,4
2,2,6,7,8
3,3,null,null,null

Not allowed:

4,5,8,8,9
or
5,6,7,9,9

TIA..


Show quoteHide quote

Could you show some samples of valid rows and invalid rows?

http://www.aspfaq.com/5006

At first I thought something like this, which is a horrible hack, but I've
re-read your narrative four times and, maybe I'm dense, but I still can't
figure out exactly what you need.


CREATE TABLE dbo.Test
(
pkid INT,
d1 INT NULL,
c1 INT NULL,
c2 INT NULL,
c3 INT NULL,
blat AS
  COALESCE(CONVERT
  (
   VARCHAR(32),

   COALESCE(RTRIM(d1),'-')
   +'_'+COALESCE(RTRIM(c1),'-')
   +'_'+COALESCE(RTRIM(c2),'-')
   +'_'+COALESCE(RTRIM(c3),'-')
  ), '')
)
GO

ALTER TABLE dbo.Test ADD CONSTRAINT foo UNIQUE(blat);
GO

INSERT dbo.Test(pkid, d1, c1, c2, c3) SELECT 1, NULL, 4, NULL, 6
INSERT dbo.Test(pkid, d1, c1, c2, c3) SELECT NULL, NULL, 4, NULL, NULL
INSERT dbo.Test(pkid, d1, c1, c2, c3) SELECT 1, NULL, 4, 5, 3
INSERT dbo.Test(pkid, d1, c1, c2, c3) SELECT 1, NULL, 4, 2, 6
INSERT dbo.Test(pkid, d1, c1, c2, c3) SELECT 1, 1, 4, NULL, 6
GO

-- fails:
INSERT dbo.Test(pkid, d1, c1, c2, c3) SELECT NULL, NULL, 4, NULL, NULL
GO

SELECT * FROM dbo.Test
GO

DROP TABLE dbo.Test
GO

This is not a table, since it has no key and has no way of getting a
key.  All the columns are NULL-able.   Was "pk_id' supposed to be the
primary key?   Do you know that the use of an explicit NULL constraint
is both proprietary and redundant?

Try again with proper declartations so we can consturct some sample
data.

Sum Total Hours and Minutes in a report
Aggregate Function Error
unique variable per connection
RFC: EXISTS (SELECT ... FROM) optional?
use subquery result in Selet statement