Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.