can this be done with a check constraint?

i have a zip code table downloaded from the usps in excel and imported
into a sql database table.
zipcode is not a primary key in the table, as it lists all the cities
and aliases for cities that include each zipcode.
however there is a rule in the data:
for any given zipcode, only ONE row of data can have a citytype value
of 'D'
what i am wondering is how i can turn that rule into a database level
constraint. i can't create a unique constraint on zipcode + citytype,
because any number of rows for the same zipcode can have citytype N and
A, for example. it is only the D value that can appear only once per
zipcode.
is this something a check constraint can accomplish? i'm new to check
constraints so i'm not sure what that constraint rule would look like.
it seems more complicated than "thisfield > thatfield" type logic.
thanks,
jasonI think this is beyond the capabilities of a check constraint.
A Check constraint is an expression which is used to check that the contents
of a column conform to a given rule. The constraint cannot reference
information on a different row of data than the one which is being checked.
You may be able to accomplish your scenario using a trigger. There are
times however, then the integrity checking would be so expensive that it's
simply more efficient to push said rule to a different business logic layer.
Colin.
"jason" <iaesun@.yahoo.com> wrote in message
news:1142884753.165122.99270@.u72g2000cwu.googlegroups.com...
>i have a zip code table downloaded from the usps in excel and imported
> into a sql database table.
> zipcode is not a primary key in the table, as it lists all the cities
> and aliases for cities that include each zipcode.
> however there is a rule in the data:
> for any given zipcode, only ONE row of data can have a citytype value
> of 'D'
> what i am wondering is how i can turn that rule into a database level
> constraint. i can't create a unique constraint on zipcode + citytype,
> because any number of rows for the same zipcode can have citytype N and
> A, for example. it is only the D value that can appear only once per
> zipcode.
> is this something a check constraint can accomplish? i'm new to check
> constraints so i'm not sure what that constraint rule would look like.
> it seems more complicated than "thisfield > thatfield" type logic.
> thanks,
> jason
>|||Jason,
2 ways to accomplish that:
1. google up "nullbuster" and create an index on computed columns
2. create an indexed view for
select zipcode from mytable where citytype = 'D'
Good luck!sql