Partitioning a Table Referenced by a Foreign Key

Question

I have one table:

CREATE TABLE [dbo].[entry]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EntryDate] [datetime] NOT NULL,
    [createddate] [datetime] NOT NULL,

    CONSTRAINT [PK_Entry_ID] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
    WITH
    (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80
    ) ON [PRIMARY]
) ON [PRIMARY];

...where the primary key is referenced by another table. To partition this table I have already done following steps :

 CREATE PARTITION FUNCTION EntryFunc (DATE) 
   AS RANGE LEFT
   FOR VALUES ('2011-01-01')


  CREATE PARTITION SCHEME EntryScheme
    AS PARTITION EntryFunc
    TO ([FileGroup2], [PRIMARY])

The above 2 steps successfully completed,but when I am partitioning table I am not able to drop primary key clustered index because it is referenced by other table. My motive is to partition the table Entry according to created date.

Answer 1

From BOL (http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx)

Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted. 

So if you want to partition a table referenced by foreign key, then both tables need to have same FK.

Also for fast partitioning, below rules apply:

you cannot use fk's

No primary key/foreign keys defined if source table has the primary key. There can be no active primary key/foreign key relationship between the source table and the target table in which the source table holds the primary key.

No primary key/foreign keys defined if target table has the foreign key. There can be no active primary key/foreign key relationship between the source table and the target table in which the target table holds the foreign key.

No foreign key from another table can reference the source table. The source table cannot be referenced by a foreign key in another table.

So, you have to drop the constraint, partition the table and the recreate the costraint. Better to do as a transaction, so on success it can be committed or on failure it can br rolled back.

Step by step tutorial can be found at : http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/ And

http://www.confio.com/logicalread/sql-server-primary-key-vs-clustered-index,-part-3/#.Ui1lNGIpDFo

Answer 2

Create a new table that is partitioned as you want and copy the data from the old table into it rather than trying to apply the new partitioning scheme to an existing table.

Once the data is copied, remove the relations to the old table, drop it, and rename the new to take the place of the old and recreate all of the relations. Perform this within a transaction for safety.

CREATE PARTITION FUNCTION EntryFunc (DATE) 
   AS RANGE LEFT
   FOR VALUES ('2011-01-01')

CREATE PARTITION SCHEME EntryScheme 
AS PARTITION EntryFunc
TO ([FileGroup2], [PRIMARY])

CREATE TABLE [dbo].[tmp_entry]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[EntryDate] [datetime] NOT NULL,
[createddate] [datetime] NOT NULL
) ON EntryScheme([EntryDate]);

SET IDENTITY_INSERT dbo.[tmp_entry] ON
IF EXISTS(SELECT * FROM dbo.[Entry])
    EXEC('INSERT INTO dbo.[tmp_entry] (ID, EntryDate, CreatedDate, /* ... */)
        SELECT ID, EntryDate, CreatedDate, /* ... */ 
        FROM dbo.[Entry] WITH (HOLDLOCK TABLOCKX)')
SET IDENTITY_INSERT dbo.[tmp_entry] OFF

IF NOT ((SELECT COUNT(*) FROM [Entry]) = (SELECT COUNT(*) FROM [tmp_entry]))
BEGIN
    PRINT 'Partition [Entry] - ABORT - Move Failed'     
END
ELSE
BEGIN
    /* DROP ALL FK CONSTRAINTS REFERENCING [Entry] */

    DROP TABLE dbo.[Entry]

    EXECUTE sp_rename N'dbo.tmp_entry', N'Entry', 'OBJECT' 

    ALTER TABLE dbo.[Entry] ADD CONSTRAINT Entry_PK PRIMARY KEY CLUSTERED  ( ID ) 
    ON [PRIMARY] /* don't add your pk to the partition scheme or it will have to include your partition key */

    /* RECREATE ALL FK CONSTRAINTS REFERENCING [Entry] */
    PRINT 'Partition [Entry] - COMPLETE'    
END