Prev: Date Questions
Next: arbeit deutschland stellenangebot mannheim jobangebote spanien Kaufmann Groß- und Außenhandel
From: Omid Golban on 6 Jul 2008 02:50

"Hugo Kornelis" <hugo(a)perFact.REMOVETHIS.info.INVALID> wrote in message news:s7uv6411qb06rb81qtvk76u0adb965aj6a(a)4ax.com...
> 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

You are absolutely correct, Vendor_To_Address_ID will be an IDENTITY column and will serve as PK.
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.

From: John Bell on 6 Jul 2008 02:52

"Omid Golban" <ogolban(a)cox.net> wrote in message
news:4A2CF564-8560-48FA-9BAF-20BB62E67896(a)microsoft.com...
> 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

I assume that you are posting this because the UseForMailing can have
multiple false values for each vendor? In which case unique/pk constraints
will not work. It is probably best to use a trigger, but you need to code
triggers for updates as well inserts. If there always has to be a
UseForMailing = 1 then you would need to decide what happens if they change
or delete the current UseForMailing = 1 entry.

John

From: Uri Dimant on 6 Jul 2008 03:07
Omid
Perhaps soemthing like that (not such elegant one)
CREATE TABLE TableA
(
[user] INT,
UseForMailing INT
)

INSERT INTO TableA VALUES (1,1)


CREATE FUNCTION dbo.fn_check_TableA
(@key int)
RETURNS bit
AS
BEGIN

IF EXISTS(SELECT [user],COUNT(UseForMailing)
FROM TableA
WHERE UseForMailing = @key AND @key=1
GROUP BY [user]
HAVING COUNT(*)>1)
RETURN 0

RETURN 1

END
GO
ALTER TABLE TableA
ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA(UseForMailing)=1)


INSERT INTO TableA VALUES (1,1) --failed
INSERT INTO TableA VALUES (1,0) --ok
INSERT INTO TableA VALUES (1,0) --ok

INSERT INTO TableA VALUES (2,1) --ok
INSERT INTO TableA VALUES (2,0) --ok
INSERT INTO TableA VALUES (2,1) --failed


DROP TABLE TableA
DROP FUNCTION dbo.fn_check_TableA



"Omid Golban" <ogolban(a)cox.net> wrote in message
news:eZ41AQz3IHA.2336(a)TK2MSFTNGP03.phx.gbl...
>
> "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


From: Erland Sommarskog on 6 Jul 2008 05:05
Omid Golban (ogolban(a)cox.net) writes:
> "Hugo Kornelis" <hugo(a)perFact.REMOVETHIS.info.INVALID> wrote in message
news:s7uv6411qb06rb81qtvk76u0adb965aj6a(a)4ax.com...
>> On Sat, 5 Jul 2008 14:23:51 -0700, Omid Golban wrote:
>> 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.
>
> You are absolutely correct, Vendor_To_Address_ID will be an IDENTITY
> column and will serve as PK.

So you mean that the same vendor could have the same address twice?
Well, obviously with the design Hugo suggestion, this could be a reality,
if the same address is used for more than once purpose. But in such case
the key should be (Vendor_ID, Address_ID, UseFor). Looking at your table,
I think the Vendor_To-Address_ID columns should not be there at all.

Surrogate keys can be useful, when there is no practical natural key, but
this does not appear to be the case here. This seems to be a typical
connection table, of which the PK should be keys of the connected
entities.

By the way, what is in this AddressType_ID column?

> 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.

This seems reasonable to me, and also I don't really like to have the
same entry vendor/address pair thrice if the same address is used for
the sama purpose (which I would expect to be common).

--
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: Erland Sommarskog on 6 Jul 2008 05:33
Omid Golban (ogolban(a)cox.net) writes:
> 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?

Well, you can have an INSTEAD OF trigger that fires instead of the INSERT;
this permits you to catch the violation earlier. But then you need to redo
the INSERT statement, so it's more coding. In practice, yes, you would do
an AFTER trigger, that would fire after the fact and raise an error.
Triggers enforces conditional uniqueness can also have poor performance
for mass inserts, so this is not a very attractive solution. I mentioned
it mainly for the sake completeness.

> 2. I am not sure how the view would help me out in this situation.

Not any view, but a view with a unique clustered index. Here is an
example:

CREATE TABLE vendoraddresses (
vendorid int NOT NULL,
addressid int NOT NULL,
useformailing bit NOT NULL,
useforPC bit NOT NULL,
useforchecks bit NOT NULL,
CONSTRAINT pk_vendaddr PRIMARY KEY (vendorid, addressid))
go
CREATE VIEW vendoraddresses_mailing WITH SCHEMABINDING AS
SELECT vendorid
FROM dbo.vendoraddresses
WHERE useformailing = 1
go
CREATE UNIQUE CLUSTERED INDEX view_ix ON vendoraddresses_mailing
(vendorid)
go
INSERT vendoraddresses
(vendorid, addressid, useformailing, useforPC, useforchecks)
VALUES (1, 1, 1, 1, 1)
go
INSERT vendoraddresses
(vendorid, addressid, useformailing, useforPC, useforchecks)
VALUES (1, 2, 1, 1, 1)
go
DROP VIEW vendoraddresses_mailing
DROP TABLE vendoraddresses

> 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.

When I think of it, I think Eric's suggestion was incorrect. Here is a
working example:

CREATE TABLE vendoraddresses (
vendorid int NOT NULL,
addressid int NOT NULL,
useformailing bit NOT NULL,
useforPC bit NOT NULL,
useforchecks bit NOT NULL,
ident int IDENTITY,
onemailingonly AS (CASE useformailing
WHEN 1 THEN vendorid
ELSE -ident
END)
CONSTRAINT pk_vendaddr PRIMARY KEY (vendorid, addressid),
CONSTRAINT u_vendaddr UNIQUE (onemailingonly))
go
INSERT vendoraddresses
(vendorid, addressid, useformailing, useforPC, useforchecks)
VALUES (1, 1, 1, 1, 1)
go
INSERT vendoraddresses
(vendorid, addressid, useformailing, useforPC, useforchecks)
VALUES (1, 2, 1, 1, 1)
go
INSERT vendoraddresses
(vendorid, addressid, useformailing, useforPC, useforchecks)
VALUES (1, 3, 0, 1, 1)
go
INSERT vendoraddresses
(vendorid, addressid, useformailing, useforPC, useforchecks)
VALUES (1, 4, 0, 1, 1)
go
SELECT * FROM vendoraddresses
go
DROP TABLE vendoraddresses

I had to restore the IDENTITY column that I suggested that you do need
in another post.

Compared to the indexed view, this is less heavy duty. On the other hand
we have dilluted the schema with no less than two superfluous columns.
It is also a bit of a hack, because it makes an assumption about the
vendor id, that it is > 0. A reasonable assumption to make, but never-
theless.


> 4. I tried the fourth solution on SQL Server 2008 and it seems to work
> okay. I have never seen this before.

Yes, this is a new feature in SQL 2008. And I think this is by far the
most attractive solution of them all. Since an index is a physical
entity, it would have been even nicer if it had been a constraint,
but the practical difference is little.

--
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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Date Questions
Next: arbeit deutschland stellenangebot mannheim jobangebote spanien Kaufmann Groß- und Außenhandel