Prev: Date Questions
Next: arbeit deutschland stellenangebot mannheim jobangebote spanien Kaufmann Groß- und Außenhandel
From: Omid Golban on 5 Jul 2008 17:23
Consider following table:

CREATE TABLE Vendor_To-Address
(
Vendor_To-Address_ID INT,
Vendor_ID INT,
Address_ID INT,
AddressType_ID INT,
UseForMailing BIT,
UseForPO BIT,
UseForChecks BIT
)

How do I make a check constraint to allow many of these records for each vendor but make sure only one of them has the column
UseForMailing = 'True' (ie only one used for mailing)?

Thank you in advance
Omid

From: Eric Isaacs on 5 Jul 2008 17:38
Create a calculated column that's equal to -1 * Vendor_To-Address_ID,
if the bit is 0, or 1 if the bit value is 1. Then create a unique
constraint on vendor_id and the calculated column together.

-Eric Isaacs

From: Erland Sommarskog on 5 Jul 2008 18:06
Omid Golban (ogolban(a)cox.net) writes:
> Consider following table:
>
> CREATE TABLE Vendor_To-Address
> (
> Vendor_To-Address_ID INT,
> Vendor_ID INT,
> Address_ID INT,
> AddressType_ID INT,
> UseForMailing BIT,
> UseForPO BIT,
> UseForChecks BIT
> )
>
> How do I make a check constraint to allow many of these records for each
> vendor but make sure only one of them has the column UseForMailing =
> 'True' (ie only one used for mailing)?

Four methods:

1) Use a trigger.

2) Create an indexed view:
SELECT .. FROM [Vendor_To-Address] WHERE UseForMailing = 1

3) Use a computed column as suggested by Eric Isaacs. For SQL 2005,
this is probably the best solution.

4) In SQL 2008, you can use a filtered index:
CREATE UNIQUE INDEX UseForMailing_ix ON
[Vendor_To-Address] (Vendor_ID)
WHERE UseForMailing = 1



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Hugo Kornelis on 5 Jul 2008 18:45
On Sat, 5 Jul 2008 14:23:51 -0700, Omid Golban wrote:

>Consider following table:
>
>CREATE TABLE Vendor_To-Address
>(
> Vendor_To-Address_ID INT,
> Vendor_ID INT,
> Address_ID INT,
> AddressType_ID INT,
> UseForMailing BIT,
> UseForPO BIT,
> UseForChecks BIT
>)
>
>How do I make a check constraint to allow many of these records for each vendor but make sure only one of them has the column
>UseForMailing = 'True' (ie only one used for mailing)?
>
>Thank you in advance
>Omid

Hi Omid,

Or you could consider an alternative design:

CREATE TABLE Vendor_To-Address
(
Vendor_To-Address_ID INT,
Vendor_ID INT,
Address_ID INT,
AddressType_ID INT,
UseFor CHAR(1) NOT NULL
CHECK(UseFor IN ('M', -- Mailing
'P', -- PO
'C')) -- Checks
)

BTW, you'll also need to define a primary key, and I think not all other
columns should allow nulls.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Omid Golban on 6 Jul 2008 02:46

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9AD312628276Yazorman(a)127.0.0.1...
> Omid Golban (ogolban(a)cox.net) writes:
>> Consider following table:
>>
>> CREATE TABLE Vendor_To-Address
>> (
>> Vendor_To-Address_ID INT,
>> Vendor_ID INT,
>> Address_ID INT,
>> AddressType_ID INT,
>> UseForMailing BIT,
>> UseForPO BIT,
>> UseForChecks BIT
>> )
>>
>> How do I make a check constraint to allow many of these records for each
>> vendor but make sure only one of them has the column UseForMailing =
>> 'True' (ie only one used for mailing)?
>
> Four methods:
>
> 1) Use a trigger.
>
> 2) Create an indexed view:
> SELECT .. FROM [Vendor_To-Address] WHERE UseForMailing = 1
>
> 3) Use a computed column as suggested by Eric Isaacs. For SQL 2005,
> this is probably the best solution.
>
> 4) In SQL 2008, you can use a filtered index:
> CREATE UNIQUE INDEX UseForMailing_ix ON
> [Vendor_To-Address] (Vendor_ID)
> WHERE UseForMailing = 1
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for the quick responses.
I didn't have much luck with triggers:
1. Trigger fires after the insert / update are done. Hence the trigger has to raise an error. Is this what you meant?
2. I am not sure how the view would help me out in this situation.
3. I played with a computed column.
The problem I ran into was this:
for every vendor I could have only 2 records:
one with UseForMailing = 0, one with UseForMailing = 1.
A third record always violated the unique constraint.
My goal is to allow many records where UseForMailing = 0, but only one of them may have UseForMailing = 1.
4. I tried the fourth solution on SQL Server 2008 and it seems to work okay.
I have never seen this before.
Of course I am very new to SQL server; I just transitioned from Pick database to SQL server earlier this year.
I really like this because it has no programming maintenance.

Thank you,
Omid
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Date Questions
Next: arbeit deutschland stellenangebot mannheim jobangebote spanien Kaufmann Groß- und Außenhandel