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

Can SQL Unzip after backup is complete

After our Daily backups are complete, I would like Sql to Open a Zip File.
Is their a command to do this within SQL?
EddieThere's no direct SQL command to open a zip file. What do you want SQL Serve
r to do with the zip
file? What does the zip file contain? Do you want to unzip the zip file and
extract the files in it?
In any event, you can use the command-line version of winzip, called wzzip,
from xp_cmdshell. If you
want to compress your backups, there are some 3:rd party apps that does this
:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:4A434EBC-738C-47BE-8C90-20883B346517@.microsoft.com...
> After our Daily backups are complete, I would like Sql to Open a Zip File.
> Is their a command to do this within SQL?
> --
> Eddie