Prev: Date Questions
Next: arbeit deutschland stellenangebot mannheim jobangebote spanien Kaufmann Groß- und Außenhandel
From: Hugo Kornelis on 6 Jul 2008 07:45
On Sat, 5 Jul 2008 23:50:54 -0700, Omid Golban wrote:

(snip)
>You are absolutely correct, Vendor_To_Address_ID will be an IDENTITY column and will serve as PK.

Hi Omid,,

Erland already addressed this. Barring some rare exceptions, no table
should have an IDENTITY column as its *only* key, and many tables don't
need an IDENTITY column at all.

>Switching from multiple UseForSomething to a single column the way you described works well if I want to enforce that every Address
>will have a UseForSomething. However, in some rare instances I may want to keep an address without any UseForSomething.

In that case, you either make the UseForSomething column nullable, or
you add a fourth value in the check constraint that can be used to
explicitly declare that the address is for nothing. However, I do wonder
about the business use of storing an address that is used for nothing...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Erland Sommarskog on 6 Jul 2008 15:20
Hugo Kornelis (hugo(a)perFact.REMOVETHIS.info.INVALID) writes:
> In that case, you either make the UseForSomething column nullable, or
> you add a fourth value in the check constraint that can be used to
> explicitly declare that the address is for nothing. However, I do wonder
> about the business use of storing an address that is used for nothing...

Make sense to me. The customer (or in this case a vendor) may have
alternate addresses. The system may not use all of them, but users like
to enter them into the system, so they can find it when they need to.
What could be useful is to have a comment column so that users can enter
comments like "Summer cottage" or similar.


--
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: Eric Isaacs on 6 Jul 2008 15:50
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: Alex Kuznetsov on 6 Jul 2008 18:41
On Jul 6, 2:50 pm, Eric Isaacs <eisa...(a)gmail.com> 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

it certainly does work, and it is well known. Google up the
following : nullbuster steve kass

From: Alex Kuznetsov on 7 Jul 2008 13:39
On Jul 6, 6:18 pm, Steve Kass <sk...(a)drew.edu> wrote:
> 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

Yes, Steve, I know.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Date Questions
Next: arbeit deutschland stellenangebot mannheim jobangebote spanien Kaufmann Groß- und Außenhandel