Unique unless null?

Is there a way to set up a constraint, or something with the effect of a 
constraint, to prevent two rows in a table from having the same non-null 
value in a particular column while allowing multiple rows to have NULL 
in that row? Consider a table, some of whose data is imported from an 
external source, and the table has a column for storing the primary key 
value associated with the imported data in its source database. In other 
words, one way of looking at this column is that it's a 1..(0, 1) 
foreign key into another table that isn't present.
0
Harlan
3/18/2010 11:37:24 PM
sqlserver.server 1327 articles. 0 followers. Follow

2 Replies
1040 Views

Similar Articles

[PageSpeed] 26

Hi Harlan

What version are you using? You should always tell us that first thing.

If you are using SQL Server 2008, you can create a filtered unique index 
with the filter WHERE <your column> IS NOT NULL.
The uniqueness would then only apply to the rows that are not null, and you 
could have as many NULLs as you like.

-- 
HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

"Harlan Messinger" <h.usenetremoverthis@gavelcade.com> wrote in message 
news:80frr8Fo5gU1@mid.individual.net...
> Is there a way to set up a constraint, or something with the effect of a 
> constraint, to prevent two rows in a table from having the same non-null 
> value in a particular column while allowing multiple rows to have NULL in 
> that row? Consider a table, some of whose data is imported from an 
> external source, and the table has a column for storing the primary key 
> value associated with the imported data in its source database. In other 
> words, one way of looking at this column is that it's a 1..(0, 1) foreign 
> key into another table that isn't present. 

0
Kalen
3/18/2010 11:43:46 PM
Kalen Delaney wrote:
> 
> Hi Harlan
> 
> What version are you using? You should always tell us that first thing.
> 
> If you are using SQL Server 2008, you can create a filtered unique index 
> with the filter WHERE <your column> IS NOT NULL.
> The uniqueness would then only apply to the rows that are not null, and 
> you could have as many NULLs as you like.
> 
Good point. Yes, it's SQL Server 2008. And, awesome, thanks! Glad I 
upgraded.
0
Harlan
3/19/2010 2:23:59 AM
Reply:

Similar Artilces:

defining unique range of cells for different sheets as the same n.
I want to use the same name, month9, to refer the same range of cells, but on different sheets, how do i turn off the global define? When you define the name (Insert|name|Define), include the sheet name in the "names in workbook" box: sheet1!month9 or 'sheet 1'!month9 You can select your range and type this in the name box (to the left of the formula bar), too. KSAPP wrote: > > I want to use the same name, month9, to refer the same range of cells, but on > different sheets, how do i turn off the global define? -- Dave Peterson ...

How can I define unique font per each Outlook profile?
We have several Outlook profiles on one computer and each user wants their own font. I noticed changing to one type of font in one profile affects all the other user profiles in Outlook. Is there a way to assign unique fonts to each Outlook profile? Many thanks experts... Each user would need their own Windows login.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

unique record numbers
I am trying to create a unique PIN for members in a table (not the primary key) that will be 4 digits long, and would like each new entry to be incremental. I am very new, and would appreciate a detailed instruction (though I can get around access fair enough). Any help would be appreciated. On Fri, 5 Mar 2010 19:19:44 -0800, Rev David Bissas <Rev David Bissas@discussions.microsoft.com> wrote: The "DMax + 1" technique should work for you. Google or Bing for it. -Tom. Microsoft Access MVP >I am trying to create a unique PIN for members in a table (not t...

Beginner: Is HWND unique?
Hi! When I start an application twice and ask each application for the handle of its main window (calling an inbuild COM function), are the returned dwords guaranteed to be different? Thanks a lot for your help! Volker -- For email replies, please substitute the obvious. It is not guaranteed to be the same or different. You might get the same HWND towice in a row, or might get different ones between two instances of your program. AliR. "Volker Hetzer" <firstname.lastname@ieee.org> wrote in message news:ed9gei$di9$1@nntp.fujitsu-siemens.com... > Hi! > When I start an ...

Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell multiple items, but I only need to show the vendor once so I can pull other information. There are also may be blanks in the rows if the vendor is inactive. Thanks! Let me add one more twist to this problem: The unique values also need to filter out where it may be 'VendorA - West' and 'VendorA - East' and combine them into one record. I only need it to show VendorA and not both records East and West (same company, just different drop ship location). Thanks! "Karl Burrows" <kfb1@spa...

Is there any a "unique" function performs on CStringArray or CStringList?
Is there any a "unique" function performs on CStringArray or CStringList? I know there is an algorithm function "unique" performing on containers in Standard C++ library. Is there a similar function in MFC? What am I doing now to avoid adding same strings to container is: CStringList strList; ..... if (strList.Find("somestring") == NULL) strList.AddTail("somestring"); But I realized it may be an inefficient way. Any good sugestion? I would have used a CMap for this. Then, when all insertions were done, I might consider converting it to a CArray...

m_pDocument Doc/View unique id for each view
I'm using the standard mfc mainform with a doc/view. While I'm in the view portion I need to track the actions taken individually and separately for each view. So when when I click on view one I track that in a separate place than view two. I there a unique id or index for each view? Something like m_pDocument->ID? Thank you, Joshua <jtfaulk@eudoramail.com> wrote in message news:1111085780.370621.117620@f14g2000cwb.googlegroups.com... > I'm using the standard mfc mainform with a doc/view. While I'm in the > view portion I need to track the actions taken...

Unique Constraints
Hello, I have a Users table with 2 fields, UserName and Visible. The same UserName can exist many times with Visible = 0 but just one record can exist for the same UserName with visible = 1. In my Stored Procedure before inserting a new visible user I check the user doesn't exist in the table with visible = 1, but that is not enough, there are cases when the Stored Procedure is called in the same moment with the same parameters and there is duplicated data. I can't define a Unique constraint because UserName with visible = 0 is not unique, what would be the best way...

Unique Values, not Unique Records
My data looks like the following: ID Type Amt 1234 A $10.00 1234 B $10.00 1234 C $10.00 I would like to find a way to get only one of the items on to a new spreadsheet, because they are duplicate records in my eyes. The Advanced filter does not work because each record is unique. Can anyone help? in the dialog for the advanced filter, make sure only the ID column is referenced "steph44haf" wrote: > My data looks like the following: > > ID Type Amt > 1234 ...

Deserialization of Null Elements
I have some XML that contains some null elements, for example: <Document> <StartDate/> <EndDate/> </Document> I would like to process the XML using the .NET serialization tools (XSD.exe). The schema for the XML is under external control (i.e. I can't change it). As far as I can tell there is no way to detect the presence of null elements (like StartDate and EndDate above) in deserialized objects (i.e. the generated objects are identical if the elements are null or if they are not present). Am I correct in my understanding of this? Can you recomm...

Find Replace values when they are not null
I have a table that contains dollar amounts in random cells throughout the table with the remainder of the cells containing nulls. I need to replace every dollar amount with a zero while not interfering with the nulls. Can someone help me with to create an update query that will accomplish this? Thanks so much. teddyb777 wrote: >I have a table that contains dollar amounts in random cells throughout the >table with the remainder of the cells containing nulls. I need to replace >every dollar amount with a zero while not interfering with the nulls. Can >someone h...

extracting unique #'s from a list
my last post was dealing with sumif function and you guys got me on th right track. Now I have column with some duplicate #'s. How can extract the unique numbers into a different column?? A1 B1 1 1 1 2 2 3 3 4 4 5 4 6 5 6 2 -- Message posted from http://www.ExcelForum.com Hi one way: use 'Data - Filter - Advanced Filter' and check 'Unique values'. Also have a look at: http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique -- Regards Frank Kabel Frankfurt, Germany > my last post was dealing with sumif function ...

Duplicates
I have 2 lists of words. Some words from list 2. are on list 1. too. Now I merge these lists into one list. How can I obtain just unique words or unique words on the top of the merged list? TIA koncept wrote: > I have 2 lists of words. Some words from list 2. are on list 1. too. > Now I merge these lists into one list. > How can I obtain just unique words or unique words on the top of the > merged list? > > TIA Check out this link provided by Pearson Software Consulting http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates gls858 Make sure your column has a...

In column A I have duplicate records. How do I tag an unique reco.
Sorry to post the question in the subject line. My question is: In column A I have duplicate records. How do I tag the unique record. Thanks a lot. Tian See Chip Pearson's "duplicate" site. http://www.cpearson.com/excel/duplicat.htm Gord Dibben Excel MVP On Thu, 13 Jan 2005 11:03:02 -0800, "Tian" <Tian@discussions.microsoft.com> wrote: >Sorry to post the question in the subject line. > >My question is: In column A I have duplicate records. How do I tag the >unique record. > >Thanks a lot. > > > ...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

A Unique ID
We know, MFC, generate a Unique ID, for each visual element incorporate to its form. I need to create a unique ID to name my files, how can I do that, without have to create my own algorithm. Is there any API for that I hope somebody can help me, thanks in advantage You can create a name which as a GUID in it. Take a look at the following API CoCreateGuid() -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Hector Martinez" <anonymous@discussions.microsoft.com> wrote in message news:5CD5F895-46FC-4211-8820-D8832D044F52@microsoft.com....

Count unique
I have a spreadsheet containing a list of names. Many names are repeated. I need to count the number of unique names in the list. Anyone got a formula for me? Thanks! Hi, If your list of names was in A1:A5 then this: =SUM(1/COUNTIF($A$1:$A$5,A1:A5) entered using Ctrl+Shift+Enter is an array formula that works. Sam "xp" wrote: > I have a spreadsheet containing a list of names. Many names are repeated. I > need to count the number of unique names in the list. Anyone got a formula > for me? > > Thanks! Hello, I suggest to look here...

Count Unique Combinations
Hello all, I'm having problems coming up with a formula to count the number of unique combinations in a list. I have a list of people in column B and a list of tasks in column C. There are duplicates in both columns. I need a formula to count the unique combinations in the list (i.e. bob smith/j. smith would count 1. Also I need to count the separtely so I know how many unique combinations are for bob smith and so on. I hope I've proviede enough info. Thank you for your assistance, Ron Col B Col C bob smith j. smith don carter r. jones alan jackson s. rober...

Return Uniques
I have 2 qry's the same thing just different criteria in RoleID. I am trying to sort by LastName. In one it lets me, in the other I get an error that my sort conflicts tieh DISTINCT. Why the different behavior? Thanks. I copy/pasted the SQL statement and changed the criteria and it works. They looked the same before though. ??? "MeSteve" wrote: > I have 2 qry's the same thing just different criteria in RoleID. I am trying > to sort by LastName. In one it lets me, in the other I get an error that my > sort conflicts tieh DISTINCT. Why the different beh...

NDR and sending null
So how can I get my Exchange 2003 servers to send NDR's with a "Return-Path: <>" ??? Right now we are sending as postmaster@mydomain.com tia On 10 Jan 2006 11:04:19 -0800, "joedonato@gmail.com" <joedonato@gmail.com> wrote: >So how can I get my Exchange 2003 servers to send NDR's with a >"Return-Path: <>" ??? > >Right now we are sending as postmaster@mydomain.com > >tia That's what's supposed to happen. Did I miss something in that question? "Mark Arnold [MVP]" <mark@mvps.org> wrote in mess...

Creating a unique number for a new entity
Hey, I have created a new entity and want one of the attributes to have a unique id so that everytime that attribute is filled in, on the form, the number entered cannot be repeated. -- Mel Hi Mel, You need to write a callout code which would be triggered by the creation of the entity. You would also need a custom field to store the ID in. Take a look at the Working with MS Dynamics CRM 3 book - I think there is an example in there. Pete. "Melanie" wrote: > Hey, I have created a new entity and want one of the attributes to have a > unique id so that everytime that a...

Summing unique values (where "unique" depends on multiple criteria")
I have three columns, date, publication, reach Date Publication Reach 1/02/2009 Herald Sun 200 1/02/2009 Herald Sun 200 3/02/2009 Herald Sun 200 3/02/2009 Herald Sun 200 5/02/2009 Herald Sun 200 6/02/2009 Herald Sun 200 7/02/2009 Herald Sun 200 8/02/2009 Herald Sun 200 9/02/2009 Herald Sun 200 9/02/2009 Herald Sun 200 1/02/2009 The Age 100 1/02/2009 The Age 100 1/02/2009 The Age 100 5/02/2009 The Age 100 5/02/2009 The Age 100 7/02/2009 The Age 100 7/02/2009 The Age 100 8/02/2009 The Age 100 9/02/2009 The Age 100 9/02/2009 The Age 100 The total of the reach column is 3...

Null to reflect as zero
Hi, I have a query with loads of tables and crosstabs. Its basically a stats report. My problem is that sometimes there are cells that are blank but i dont want that. I'd prefer it to reflect as a zero. how do i do that? Er, im no sql boffen but im guessing you are going to ask for that so here it is. LOL Please dont reply in sql language as i wouldnt know what to do with it :) Ta Whoops... Here we go: SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate, suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation, tblClientContact.txtCom...

how can i put a unique number in a header for a git certificat for
If i were printing gift certificates, in excel, and I wanted to put a unique number on each one that was printed, how would i do this I know i can put the time and date and page number on the footer, but that number does'nt change can someone please help You can generate a unique number with: =SUBSTITUTE(NOW(),".","") HTH Jason Atlanta, GA >-----Original Message----- >If i were printing gift certificates, in excel, and I wanted to put a unique >number on each one that was printed, how would i do this >I know i can put the time and date and page numb...

Unique lot numbers
We have switched from using serial numbers to lot numbers on a number of our products, as we must track additional data with each item. The only problem that we (i.e. I) didn't forsee was that you can have multiple identical lot numbers. I know this is obvious, but it just didn't click. Otherwise lot numbers makes perfect sense for us. Can I make the lot # field a primary key, or otherwise restrict it to unique entries? The way we're set up, we dont have any items that would require duplicate lot numbers, nor would we. Thanks for any thoughts. GP 9.0, SQL Server 2000. ...