I'd like to keep it in...

Yes, I...

Not a bad idea, but that someone would most likely be me, so I think it's better to build triggers that prevent the situation from occurring in the first...

Now that I think about it some more, I'm not even sure that it's possible to enforce an 'exactly one' condition, even with code. It would never be possible for...

Thanks for the tip. I'm looking at your blog, but it's a bit technical and...

GilaMonster (5/25/2015)

---

CREATE UNIQUE INDEX idx_TableOfClovekNames_ClovekAutoID

ON TableOfClovekNames (ClovekAutoID)

WHERE JeHlavni = 1

The unique requirement on that only applies to rows where JeHlavni is 1, and it forces that there may not be...

GilaMonster (5/25/2015)

---

I would use a filtered index for that (well, part of that).

You want to ensure that there is only ever one row with JeHlavni = 1 for each...

dwain.c (5/19/2015)

---

Part of the issue I had with your question is in this paragraph. You say "indexed on the appropriate fields" but unfortunately that is a rather subjective statement....

That was a question, not a conclusion. Notice the question mark at the end of the first sentence. The second was simply a restatement of what I'm trying to do,...

dwain.c (5/18/2015)

---

Another way:

SELECT *

INTO #Test

FROM (Values

(1, 'A'),

(1, 'B'),

(1, 'C'),

(1, 'D'),

(1, 'E'),

(2, 'A'),

(2, 'C'),

(2, 'D'),

(2, 'E'),

(3, 'A'),

(3, 'B'),

(3, 'C'),

(3, 'F'),

(4, 'A'),

(4, 'B'),

(4, 'D'),

(4, 'F'),

(4, 'G'))x(id, value);

SELECT id

...

Got it - fat-fingered an edit when I copied out some parts of the query for my original post. Works great - many thanks, Luis. Complex execution plan, that's going...

Well, here's the actual query, if you want to take a stab at it, but I haven't assembled all the necessary TSQL to give you the entire data environment on...

That looks promising, but I'm getting an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when...

Thank you everyone for the input and suggestions. I've decided to go with a trigger, largely because everything is in one place. The check constraint option has the disadvantage of...

Emil Bialobrzeski (12/11/2014)

---

Yes the parameters are from the incoming record while you do an insert

ALTER TABLE Test ADD CONSTRAINT CK_Test CHECK (dbo.CKTest(ID, Letter, Number, OptionalLetter) = 0)

EDIT:

Use the code...