Prev: How to execute SP that returns a select set from another SP
Next: Need help!
From: Steve Kass on 6 Jul 2008 19:12
Eric,

You can avoid the assumption on VendorToAddressID in one of the
following ways, each of which allows VendorUseForMailing_UNIQUE
to equal VendorToAddressID for non-mailing addresses by using a
value not in the key domain (INT NOT NULL) in the mailing address
case.

One possibility is to take advantage of the fact that SQL Server
violates the SQL standard when it comes to UNIQUE constraints
and NULLs. By design, SQL Server allows only one NULL value
in a column with a UNIQUE constraint, so you could use NULL
as the extra value, since it can't be a key value:

VendorUseForMailing_UNIQUE AS (CASE UseForMailing
WHEN 1 THEN NULL
ELSE VendorToAddressID
END)

Alternatively (to preserve forward compatibility should SQL Server
ever implement ANSI UNIQUE constraints), use a domain for
VendorUseForMailing_UNIQUE that contains all INT NOT NULL
values and more:

VendorUseForMailing_UNIQUE AS (CASE UseForMailing
WHEN 1 THEN CAST(10000000000 AS BIGINT)
ELSE VendorToAddressID
END)

Steve Kass
Drew University
http://www.stevekass.com

Eric Isaacs wrote:

>Erland,
>
>I think my suggestion would work, assuming that the VendorToAddressID
>was a unique positive number (INT IDENTITY, or just a unique INT
>that's greater than zero.)
>
>Here's a working example with comments. The second insert should
>fail, but all the others should succeed.
>
>CREATE TABLE VendorToAddress
>(
> VendorToAddressID INT IDENTITY NOT NULL,
> VendorID INT NOT NULL,
> AddressID INT NOT NULL,
> UseForMailing BIT NOT NULL,
> UseForPurchaseOrder BIT NOT NULL,
> UseForChecks BIT NOT NULL,
> VendorUseForMailing_UNIQUE AS (CASE UseForMailing
> WHEN 1 THEN 1
> ELSE -VendorToAddressID
> END)
> CONSTRAINT pk_vendaddr PRIMARY KEY (VendorToAddressID),
> CONSTRAINT u_vendoraddress UNIQUE (VendorID, AddressID),
> CONSTRAINT u_VendorMailingAddress UNIQUE (VendorID,
>VendorUseForMailing_UNIQUE)
>)
>
>GO
> SET NOCOUNT OFF
>
> PRINT 'This next insert should succeed.'
>
> INSERT
> VendorToAddress
> (
> VendorID,
> AddressID,
> UseForMailing,
> UseForPurchaseOrder,
> UseForChecks
> )
> VALUES
> (1, 1, 1, 1, 1)
>
>
>GO
> PRINT 'This next insert should fail because a vendor can have only
>one address used for mailing.'
>
> INSERT
> VendorToAddress
> (
> vendorid,
> addressid,
> useformailing,
> UseForPurchaseOrder,
> useforchecks
> )
> VALUES
> (1, 2, 1, 1, 1)
>
>GO
> PRINT 'This next two inserts should succeed because a vendor can
>have multiple addresses, as long as they are different and only one is
>used for mailing.'
>
> INSERT
> VendorToAddress
> (
> vendorid,
> addressid,
> useformailing,
> UseForPurchaseOrder,
> useforchecks
> )
> VALUES
> (1, 2, 0, 1, 1)
>
>
>GO
> INSERT
> VendorToAddress
> (
> vendorid,
> addressid,
> useformailing,
> UseForPurchaseOrder,
> useforchecks
> )
> VALUES
> (1, 4, 0, 1, 1)
>
>GO
> PRINT 'This next three inserts should succeed because another
>vendor can have one mailing address and multiple non-mailing
>addresses.'
>
> INSERT
> VendorToAddress
> (
> vendorid,
> addressid,
> useformailing,
> UseForPurchaseOrder,
> useforchecks
> )
> VALUES
> (2, 5, 1, 1, 1)
>
>GO
> INSERT
> VendorToAddress
> (
> vendorid,
> addressid,
> useformailing,
> UseForPurchaseOrder,
> useforchecks
> )
> VALUES
> (2, 6, 0, 1, 1)
>GO
> INSERT
> VendorToAddress
> (
> vendorid,
> addressid,
> useformailing,
> UseForPurchaseOrder,
> useforchecks
> )
> VALUES
> (2, 7, 0, 0, 0)
>
>GO
> SET NOCOUNT ON
> SELECT * FROM VendorToAddress
>
>GO
> DROP TABLE VendorToAddress
>
>
From: Steve Kass on 6 Jul 2008 19:18
Thanks, Alex. While I suggested the name "nullbuster" for the computed
column, Ivan Arjentinski posted the idea a few months before I did.

SK

Alex Kuznetsov wrote:

>
> it certainly does work, and it is well known. Google up the
> following : nullbuster steve kass
>
 | 
Pages: 1
Prev: How to execute SP that returns a select set from another SP
Next: Need help!