title: How to represent a value that's not null except for one row?
body: <p>I'm attempting to model the entities for a resume and in doing so I came to the following issue: <strong>all except the most recently held positions have an end date</strong>.</p>
  
  <p>Is there a good way to constrain at most one row allowed to be null.  Event better would be that the single allowed null also had the latest start date.</p>
  
  <p>Another way of saying this is can you specify a constraint that says "not null unless"?</p>
  
  <p>Note: this is a learning exercise I'm doing (as opposed to working on production code).
  Here's the scripted table I'm working with:</p>
  
  <pre><code>CREATE TABLE [dbo].[Employers](
      [Id] [uniqueidentifier] NOT NULL,
      [City] [nvarchar](max) NULL,
      [State] [nvarchar](max) NULL,
      [StartMonth] [int] NULL,
      [StartYear] [int] NOT NULL,
      [EndMonth] [int] NULL,
      [EndYear] [int] NULL,
      [Name] [nvarchar](max) NULL,
      [Label] [nvarchar](max) NULL,
      [Resume_Id] [uniqueidentifier] NULL,
      [UserProfile_UserId] [int] NULL,
  PRIMARY KEY CLUSTERED 
  (
      [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  
  GO
  
  ALTER TABLE [dbo].[Employers]  WITH CHECK ADD  CONSTRAINT [Resume_Employers] FOREIGN KEY([Resume_Id])
  REFERENCES [dbo].[Resumes] ([Id])
  GO
  
  ALTER TABLE [dbo].[Employers] CHECK CONSTRAINT [Resume_Employers]
  GO
  
  ALTER TABLE [dbo].[Employers]  WITH CHECK ADD  CONSTRAINT [UserProfile_Employers] FOREIGN KEY([UserProfile_UserId])
  REFERENCES [dbo].[UserProfile] ([UserId])
  GO
  
  ALTER TABLE [dbo].[Employers] CHECK CONSTRAINT [UserProfile_Employers]
  GO
  </code></pre>