Prev: ROLLBACK on error
Next: Problem with Update Trigger
From: Iain Sharp on 19 May 2010 08:17
On Wed, 19 May 2010 07:57:32 -0400, "sloan" <sloan(a)ipass.net> wrote:

>
>I ~did provide complete DDL and sample data and desired results in my
>~~original~~ post.
>
>Please look at my entire original post.
>
>
>
>
>"Michael MacGregor" <nospam(a)nospam.com> wrote in message
>news:uJf1Ojw9KHA.1892(a)TK2MSFTNGP05.phx.gbl...
>> Need more information to be able to answer this more meaningfully: What
>> are the tables and columns? Can you provide example data?
>>
>> Michael MacGregor
>>
>

For those of use reading this usenet group via NNTP, your original
post is in a different thread, with no sane way of returning to
it......
From: sloan on 19 May 2010 09:11

Thanks Erland.

The filtered index was what I was looking for.




"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D7D6898548BBYazorman(a)127.0.0.1...
> sloan (sloan(a)ipass.net) writes:
>> SqlServer 2008 Standard, sometimes 2005.
>>
>>
>>
>>
>>
>> Question #1:
>>
>> Is there anyway to create a constraint that would allow (in my sample)
>> 1 "Primary Email" ~per Person ?
>
> In SQL 2008 you can do this with filtered indexes.
>
> CREATE UNIQUE INDEX ix ON tbl(Person) WHERE primary_email = 1
>
> In SQL 2005 you use
>
> 1) trigger
> 2) an indexed view with the WHERE condition above.
> 3) it is also usually possible to this with an indexed computed column.
>
>> Question #2:
>> Along the same lines, any way to make sure at least 1 primary email
>> exists
>> before adding non primary emails (per person)?
>
> That needs to be a trigger.
>
> --
> 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: sloan on 19 May 2010 09:17
The post to which he (Michael MacGregor) replied ...... was the original
post. Aka, the post with the complete DDL and example data and desired
results.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/9eec092c063ae6c7#


"Iain Sharp" <iains(a)pciltd.co.uk> wrote in message
news:mll7v5dgldrc46fnile1c7pe1gt1978va2(a)4ax.com...
> On Wed, 19 May 2010 07:57:32 -0400, "sloan" <sloan(a)ipass.net> wrote:
>
>>
>>I ~did provide complete DDL and sample data and desired results in my
>>~~original~~ post.
>>
>>Please look at my entire original post.
>>
>>
>>
>>
>>"Michael MacGregor" <nospam(a)nospam.com> wrote in message
>>news:uJf1Ojw9KHA.1892(a)TK2MSFTNGP05.phx.gbl...
>>> Need more information to be able to answer this more meaningfully: What
>>> are the tables and columns? Can you provide example data?
>>>
>>> Michael MacGregor
>>>
>>
>
> For those of use reading this usenet group via NNTP, your original
> post is in a different thread, with no sane way of returning to
> it......


From: sloan on 19 May 2010 10:02

The final syntax was:

CREATE UNIQUE INDEX IX_EmailAddress_One_PrimaryEmail_Per_Person ON
[dbo].[EmailAddress](PersonUUID,IsPrimary) WHERE IsPrimary = 1




"sloan" <sloan(a)ipass.net> wrote in message
news:%23YjwST19KHA.4308(a)TK2MSFTNGP04.phx.gbl...
>
> Thanks Erland.
>
> The filtered index was what I was looking for.
>
>
>
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9D7D6898548BBYazorman(a)127.0.0.1...
>> sloan (sloan(a)ipass.net) writes:
>>> SqlServer 2008 Standard, sometimes 2005.
>>>
>>>
>>>
>>>
>>>
>>> Question #1:
>>>
>>> Is there anyway to create a constraint that would allow (in my sample)
>>> 1 "Primary Email" ~per Person ?
>>
>> In SQL 2008 you can do this with filtered indexes.
>>
>> CREATE UNIQUE INDEX ix ON tbl(Person) WHERE primary_email = 1
>>
>> In SQL 2005 you use
>>
>> 1) trigger
>> 2) an indexed view with the WHERE condition above.
>> 3) it is also usually possible to this with an indexed computed column.
>>
>>> Question #2:
>>> Along the same lines, any way to make sure at least 1 primary email
>>> exists
>>> before adding non primary emails (per person)?
>>
>> That needs to be a trigger.
>>
>> --
>> 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: --CELKO-- on 20 May 2010 00:10
Why did you use a singular name for sets with more than one member?
Have you looked at ISO-11179 or any book on RDBMS?

The SSN is CHAR(9) by definition; why did you invite crappy data with
an improper declaration?

Why did you use all those needless UNIQUEIDENTIFIER columns? Why do
you have no keys in these non-tables? I know they look a lot like
pointers from the 1970's pre-RDBMS products, but we don't think that
way now.

Why did you use BIT flags as if this was Assembly language in the
1950's?

Why do you have no DRI?

If you had a relational design, would it be more like this?

CREATE TABLE Persons
(ssn CHAR(9) NOT NULL PRIMARY KEY);

CREATE TABLE Email_Addressees
(ssn CHAR(9) NOT NULL
REFERENCES Persons (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
email_priority INTEGER NOT NULL
CHECK (email_priority > 0),
PRIMARY KEY (ssn, email_priority),
email_address VARCHAR(128) NOT NULL
);

SQL programmers think in terms of sets and silly bit flags in a punch
card:

CREATE VIEW Primary_Email_Addressees (ssn, email_address)
AS
SELECT E1.ssn, E1.email_address
FROM Email_Addresses AS E1
WHERE email_priority
= (SELECT MIN (E2.email_priority)
FROM Email_Addresses AS E2
WHERE S1.ssn = S2.ssn);

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: ROLLBACK on error
Next: Problem with Update Trigger