title: Partitioning a Table Referenced by a Foreign Key
body: <p>Around 20 tables are partitioned on monthly base. They all have a date column, as the partition key. So far so good, but many of the partitioned tables are  referenced by one or more  foreign keys. This prevent me from doing a switch-out + drop partition. Because SQL Server give the error:</p>
  
  <blockquote>
    <p>Msg 50000, Level 16, State 1, Procedure HandleError, Line 26 [Batch Start Line 0]</p>
    
    <p>The table must not be referenced by a foreign key constraint.</p>
  </blockquote>
  
  <p>A possibility is to drop constraints before the switch-out and to recreate them after the switch out, as you can see in the screenshot there are a lot FK’s. Another option is to execute delete statement on every impacted table before the drop of the oldest partition. But this will take time because we are talking about millions of records / partition.</p>
  
  <p>In Oracle this seems to be no problem. As soon as Oracle sees that there are no references any more from the FK to the PK the partition can be dropped. In SQL server not.  I tried it out just to be sure.</p>
  
  <p>Does any one have a better idea how to solve this problem in an elegant way?</p>
  
  <p><img src="https://i.stack.imgur.com/UX5gz.jpg" alt="Picture with all partitioned tables with their reference constraints"></p>