Constraint Question .. 1:N Relationship with a "Primary" Attribute
in [Programming SQL]
|
Prev: ROLLBACK on error
Next: Problem with Update Trigger
From: sloan on 18 May 2010 14:19 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 ? Question #2: Along the same lines, any way to make sure at least 1 primary email exists before adding non primary emails (per person)? (Aka, you wouldn't be able to add a non-primary withOUT a pre-existing primary email?) I'm hoping for a CONSTRAINT. But I appreciate any ideas. Thanks. (The below data shows some sample data.......where a single person has 1 primary email, but N number of non primaries my request is trying to prevent more than 1 primary email being set.......per person) IsPrimary EmailAddressValue --------- -------------------- 1 john(a)hotmail.com 0 john(a)gmail.com 0 john(a)yahoo.com 1 mary(a)hotmail.com 0 mary(a)gmail.com 0 mary(a)yahoo.com John can only have 1 primary email. But he can have N number of non-primary emails. Same thing for Mary: Mary can only have 1 primary email. But she can have N number of non-primary emails. Full DDL and Sample data below with desired results: The sample is a made up one (aka, my real world need has nothing to do with email addresses)....but gets the idea across. SET NOCOUNT ON GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EmailAddress]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[EmailAddress] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[Person] END GO CREATE TABLE [dbo].[Person] ( PersonUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() , SSN varchar(11) not null ) GO ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonUUID) GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EmailAddress]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[EmailAddress] END GO CREATE TABLE [dbo].[EmailAddress] ( EmailAddressUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() , PersonUUID [UNIQUEIDENTIFIER] NOT NULL , IsPrimary bit not null default 0 , EmailAddressValue varchar(128) not null ) GO ALTER TABLE dbo.EmailAddress ADD CONSTRAINT PK_EmailAddress PRIMARY KEY NONCLUSTERED (EmailAddressUUID) GO ALTER TABLE [dbo].[EmailAddress] --You could argue that an email has to be unique across the board...but roll with it , its just a metaphor ADD CONSTRAINT CK_PersonUUID_EmailAddressValue_Unique UNIQUE (PersonUUID,EmailAddressValue) GO ALTER TABLE [dbo].[EmailAddress] ADD CONSTRAINT FK_EmailAddressToPerson FOREIGN KEY (PersonUUID) REFERENCES dbo.Person (PersonUUID) GO declare @PersonUUID1 [UNIQUEIDENTIFIER] declare @PersonUUID2 [UNIQUEIDENTIFIER] select @PersonUUID1 = '00000000-0000-0000-0000-000000000101' select @PersonUUID2 = '00000000-0000-0000-0000-000000000202' INSERT INTO dbo.Person ( PersonUUID , SSN ) select @PersonUUID1 , '111-11-1111' UNION SELECT @PersonUUID2 , '222-22-2222' Select * from dbo.Person declare @EmailAddressUUID1 [UNIQUEIDENTIFIER] declare @EmailAddressUUID2 [UNIQUEIDENTIFIER] declare @EmailAddressUUID3 [UNIQUEIDENTIFIER] declare @EmailAddressUUID4 [UNIQUEIDENTIFIER] declare @EmailAddressUUID5 [UNIQUEIDENTIFIER] declare @EmailAddressUUID6 [UNIQUEIDENTIFIER] select @EmailAddressUUID1 = 'EEEEEEEE-0000-0000-0000-000000000111' select @EmailAddressUUID2 = 'EEEEEEEE-0000-0000-0000-000000000112' select @EmailAddressUUID3 = 'EEEEEEEE-0000-0000-0000-000000000113' select @EmailAddressUUID4 = 'EEEEEEEE-0000-0000-0000-000000000221' select @EmailAddressUUID5 = 'EEEEEEEE-0000-0000-0000-000000000222' select @EmailAddressUUID6 = 'EEEEEEEE-0000-0000-0000-000000000223' INSERT INTO [dbo].[EmailAddress] ( EmailAddressUUID , PersonUUID , EmailAddressValue ) Select @EmailAddressUUID1 , @PersonUUID1 , 'john(a)hotmail.com' UNION Select @EmailAddressUUID2 , @PersonUUID1 , 'john(a)gmail.com' UNION Select @EmailAddressUUID3 , @PersonUUID1 , 'john(a)yahoo.com' UNION Select @EmailAddressUUID4 , @PersonUUID2 , 'mary(a)hotmail.com' UNION Select @EmailAddressUUID5 , @PersonUUID2 , 'mary(a)gmail.com' UNION Select @EmailAddressUUID6 , @PersonUUID2 , 'mary(a)yahoo.com' select * from [dbo].[EmailAddress] --==================================== --==================================== --This would be OK. One prmary email for John. Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressUUID = @EmailAddressUUID1 --This would FAIL. Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressUUID = @EmailAddressUUID2 --This would FAIL Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressUUID = @EmailAddressUUID3 --RESET Update dbo.EmailAddress Set IsPrimary = 0 --This would be OK with the sample data. John has one primary email, Mary has one primary email. Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressValue like '%hotmail%' --This would FAIL Update dbo.EmailAddress Set IsPrimary = 1 select * from dbo.EmailAddress
From: Eric Isaacs on 18 May 2010 15:03 Question 1 is Yes, you can do it with a trigger. Question 2 depends. It can be done with a trigger too...but what happens when a user attempts to delete a primary email? Is that allowed? What happens when a person wants to change one email to primary and make the previous primary email non-primary? Either you have two primary emails at one time or you have a person with no primary email for a short time. For this reason, I don't think you're going to be able to enforce #2 in the database, unless you automatically change any pre-existing primary emails to non-primary if a new primary email is defined. You also have to watch bulk inserts with your trigger processing. If I insert two emails with one insert statement and a union query that inserts two primary emails for the same person. Your trigger will need to handle that situation as well as a pre-existing primary email in the table before the insert. I think #2 is doable, but you need to define your business rules to make it doable. Another way to enforce this without triggers would be to put the primary email in it's own field in the person table and make it required. That's not exactly normalized, but it's easier to enforce. The trick there would be to avoid duplicates in the email table that are already in the primary email address field. I would opt for the trigger option rather than denormalizing the data, but it is an option. -Eric Isaacs > Question #1: > > Is there anyway to create a constraint that would allow (in my sample) > 1 "Primary Email" ~per Person ? > > Question #2: > Along the same lines, any way to make sure at least 1 primary email exists > before adding non primary emails (per person)? > (Aka, you wouldn't be able to add a non-primary withOUT a pre-existing > primary email?) > > I'm hoping for a CONSTRAINT. But I appreciate any ideas. > > Thanks. > > (The below data shows some sample data.......where a single person has 1 > primary email, but N number of non primaries > my request is trying to prevent more than 1 primary email being > set.......per person) > > IsPrimary EmailAddressValue > > --------- -------------------- > > 1 j...(a)hotmail.com > > 0 j...(a)gmail.com > > 0 j...(a)yahoo.com > > 1 m...(a)hotmail.com > > 0 m...(a)gmail.com > > 0 m...(a)yahoo.com > > John can only have 1 primary email. But he can have N number of non-primary > emails. > Same thing for Mary: Mary can only have 1 primary email. But she can have N > number of non-primary emails. > > Full DDL and Sample data below with desired results: > The sample is a made up one (aka, my real world need has nothing to do with > email addresses)....but gets the idea across. > > SET NOCOUNT ON > > GO > > IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = > object_id(N'[dbo].[EmailAddress]') and OBJECTPROPERTY(id, N'IsUserTable') = > 1) > > BEGIN > > DROP TABLE [dbo].[EmailAddress] > > END > > GO > > IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = > object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) > > BEGIN > > DROP TABLE [dbo].[Person] > > END > > GO > > CREATE TABLE [dbo].[Person] > > ( > > PersonUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() > > , SSN varchar(11) not null > > ) > > GO > > ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person > > PRIMARY KEY NONCLUSTERED (PersonUUID) > > GO > > IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = > object_id(N'[dbo].[EmailAddress]') and OBJECTPROPERTY(id, N'IsUserTable') = > 1) > > BEGIN > > DROP TABLE [dbo].[EmailAddress] > > END > > GO > > CREATE TABLE [dbo].[EmailAddress] > > ( > > EmailAddressUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() > > , PersonUUID [UNIQUEIDENTIFIER] NOT NULL > > , IsPrimary bit not null default 0 > > , EmailAddressValue varchar(128) not null > > ) > > GO > > ALTER TABLE dbo.EmailAddress ADD CONSTRAINT PK_EmailAddress > > PRIMARY KEY NONCLUSTERED (EmailAddressUUID) > > GO > > ALTER TABLE [dbo].[EmailAddress] > --You could argue that an email has to be unique across the board...but roll > with it , its just a metaphor > > ADD CONSTRAINT CK_PersonUUID_EmailAddressValue_Unique UNIQUE > (PersonUUID,EmailAddressValue) > > GO > > ALTER TABLE [dbo].[EmailAddress] > > ADD CONSTRAINT FK_EmailAddressToPerson > > FOREIGN KEY (PersonUUID) REFERENCES dbo.Person (PersonUUID) > > GO > > declare @PersonUUID1 [UNIQUEIDENTIFIER] > > declare @PersonUUID2 [UNIQUEIDENTIFIER] > > select @PersonUUID1 = '00000000-0000-0000-0000-000000000101' > > select @PersonUUID2 = '00000000-0000-0000-0000-000000000202' > > INSERT INTO dbo.Person ( PersonUUID , SSN ) > > select @PersonUUID1 , '111-11-1111' UNION SELECT @PersonUUID2 , > '222-22-2222' > > Select * from dbo.Person > > declare @EmailAddressUUID1 [UNIQUEIDENTIFIER] > > declare @EmailAddressUUID2 [UNIQUEIDENTIFIER] > > declare @EmailAddressUUID3 [UNIQUEIDENTIFIER] > > declare @EmailAddressUUID4 [UNIQUEIDENTIFIER] > > declare @EmailAddressUUID5 [UNIQUEIDENTIFIER] > > declare @EmailAddressUUID6 [UNIQUEIDENTIFIER] > > select @EmailAddressUUID1 = 'EEEEEEEE-0000-0000-0000-000000000111' > > select @EmailAddressUUID2 = 'EEEEEEEE-0000-0000-0000-000000000112' > > select @EmailAddressUUID3 = 'EEEEEEEE-0000-0000-0000-000000000113' > > select @EmailAddressUUID4 = 'EEEEEEEE-0000-0000-0000-000000000221' > > select @EmailAddressUUID5 = 'EEEEEEEE-0000-0000-0000-000000000222' > > select @EmailAddressUUID6 = 'EEEEEEEE-0000-0000-0000-000000000223' > > INSERT INTO [dbo].[EmailAddress] > > ( EmailAddressUUID , PersonUUID , EmailAddressValue ) > > Select > > @EmailAddressUUID1 , @PersonUUID1 , 'j...(a)hotmail.com' UNION > > Select > > @EmailAddressUUID2 , @PersonUUID1 , 'j...(a)gmail.com' UNION > > Select > > @EmailAddressUUID3 , @PersonUUID1 , 'j...(a)yahoo.com' UNION > > Select > > @EmailAddressUUID4 , @PersonUUID2 , 'm...(a)hotmail.com' UNION > > Select > > @EmailAddressUUID5 , @PersonUUID2 , 'm...(a)gmail.com' UNION > > Select > > @EmailAddressUUID6 , @PersonUUID2 , 'm...(a)yahoo.com' > > select * from [dbo].[EmailAddress] > > --==================================== > > --==================================== > > --This would be OK. One prmary email for John. > > Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressUUID = > @EmailAddressUUID1 > > --This would FAIL. > > Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressUUID = > @EmailAddressUUID2 > > --This would FAIL > > Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressUUID = > @EmailAddressUUID3 > > --RESET > > Update dbo.EmailAddress Set IsPrimary = 0 > > --This would be OK with the sample data. John has one primary email, Mary > has one primary email. > > Update dbo.EmailAddress Set IsPrimary = 1 where EmailAddressValue like > '%hotmail%' > > --This would FAIL > > Update dbo.EmailAddress Set IsPrimary = 1 > > select * from dbo.EmailAddress
From: Michael MacGregor on 19 May 2010 00:07 Need more information to be able to answer this more meaningfully: What are the tables and columns? Can you provide example data? Michael MacGregor
From: Erland Sommarskog on 19 May 2010 04:16 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 07:57
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 > |