title: Sql Server Primary Key With Partition Issue
body: <p>I am building a table that will be partitioned and contain a <code>FILESTREAM</code> column.  The issue I am encountering is that it appears I have to have a composite primary key (<code>FILE_ID</code> and <code>FILE_UPLOADED_DATE</code>) because <code>FILE_UPLOADED_DATE</code> is part of my partition scheme.  Is that correct? I would prefer not to have this be a composite key and simply just have <code>FILE_ID</code> being the primary key.....could this be just an user error?</p>
  
  <p>Any suggestions would be appreciated.</p>
  
  <blockquote>
    <p>Version: SQL Server 2008 R2</p>
  </blockquote>
  
  <p>Partition Schemes and Function:</p>
  
  <pre><code>CREATE PARTITION FUNCTION DocPartFunction (datetime)
  AS RANGE RIGHT FOR VALUES ('20101220')
  GO
  CREATE PARTITION SCHEME DocPartScheme AS
  PARTITION DocPartFunction TO (DATA_FG_20091231, DATA_FG_20101231);
  GO
  CREATE PARTITION SCHEME DocFSPartScheme AS
  PARTITION DocPartFunction TO (FS_FG_20091231,FS_FG_20101231);
  GO
  </code></pre>
  
  <p>Create Statement:</p>
  
  <pre><code>CREATE TABLE [dbo].[FILE](
      [FILE_ID] [int] IDENTITY(1,1) NOT NULL,
      [DOCUMENT] [varbinary](max) FILESTREAM  NULL,
      [FILE_UPLOADED_DATE] [datetime] NOT NULL,
      [FILE_INT] [int] NOT NULL,
      [FILE_EXTENSION] [varchar](10) NULL,
      [DocGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL UNIQUE ON [PRIMARY],
  CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
      (   [FILE_ID] ASC
      ) ON DocPartScheme ([FILE_UPLOADED_DATE])
  )ON DocPartScheme ([FILE_UPLOADED_DATE])
  FILESTREAM_ON DocFSPartScheme;
  </code></pre>
  
  <p>Error if I don't include <code>FILE_UPLOADED_DATE</code>:</p>
  
  <pre><code>Msg 1908, Level 16, State 1, Line 1
  Column 'FILE_UPLOADED_DATE' is partitioning column of the index 'PK_File'. Partition columns for a unique index must be a subset of the index key.
  Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.
  </code></pre>
  
  <p>Thanks!</p>