Limit to one value in fld b for given value in field a

Okay, i have a problem, which would be easy in a well-normalized db,
but im working in a legacy app here, and can't normalize the db because
it would break the existing apps around it.
I have a manifest table which essentially acts as both a header & line-items
table.
There is a MANIFESTID field which serves to link the lineitems together onto
one
manifest. There is also an ACTION field, which serves to indicate whether
the
manifest is for delivery or for pickup.
I dont know if it really is this hard, or if im just brain-freezing here,
but here is what
i'm trying to do... i want to put in some sort of constraint so that for all
the lines which
have any given MANIFESTID, the ACTION field is the same on all of them.
Thanks in advance,
- Arthur Dent.Not sure if I fully understood your problem, but in SQL Server, you cannot
create a constraint declaratively which can enforce some logic based on
multiple rows.
A general alternative is to use a after trigger with a rollback statement.
This is somewhat prohibitive in high performance systems since the logic can
turn out to be complex depending on your schema. In rare cases, you might be
able to work it out with a scalar udf used in a check constraint as well.
Anith|||As example for clarification,
ACTION is declared with a check as either D or P or Delivery or Pickup.
I might have 5 rows where the ManifestID is <10>. What i want is to be able
to say
For All Rows Where MANIFESTID = 10,
ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
must be 'D' or ALL must be 'P'
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23Y4JaqG7FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Not sure if I fully understood your problem, but in SQL Server, you cannot
> create a constraint declaratively which can enforce some logic based on
> multiple rows.
> A general alternative is to use a after trigger with a rollback statement.
> This is somewhat prohibitive in high performance systems since the logic
> can turn out to be complex depending on your schema. In rare cases, you
> might be able to work it out with a scalar udf used in a check constraint
> as well.
> --
> Anith
>|||On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:

>As example for clarification,
>ACTION is declared with a check as either D or P or Delivery or Pickup.
>I might have 5 rows where the ManifestID is <10>. What i want is to be able
>to say
>For All Rows Where MANIFESTID = 10,
>ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
>must be 'D' or ALL must be 'P'
Hi Arthur,
This might be one of those cases where a UDF + CHECK constraint will
work:
CREATE FUNCTION dbo.CheckNoOthers
(@.ManifestID int, @.Action char(1))
RETURNS char(3)
AS
BEGIN
DECLARE @.Result char(3)
IF EXISTS (SELECT *
FROM YourTable
WHERE ManifestID = @.ManifestID
AND Action <> @.Action)
BEGIN
SET @.Result = 'No'
END
ELSE
BEGIN
SET @.Result = 'Yes'
END
RETURN @.Result
go
ALTER TABLE YourTable
ADD CONSTRAINT NoOthers
CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yah, that looks like it would probably work. Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qgq1o1pc00til4dvnk7h2jk7nssscm6vk2@.
4ax.com...
> On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:
>
> Hi Arthur,
> This might be one of those cases where a UDF + CHECK constraint will
> work:
> CREATE FUNCTION dbo.CheckNoOthers
> (@.ManifestID int, @.Action char(1))
> RETURNS char(3)
> AS
> BEGIN
> DECLARE @.Result char(3)
> IF EXISTS (SELECT *
> FROM YourTable
> WHERE ManifestID = @.ManifestID
> AND Action <> @.Action)
> BEGIN
> SET @.Result = 'No'
> END
> ELSE
> BEGIN
> SET @.Result = 'Yes'
> END
> RETURN @.Result
> go
> ALTER TABLE YourTable
> ADD CONSTRAINT NoOthers
> CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
> go
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)