Sql-Server

對外鍵引用的表進行分區

  • May 28, 2020

我有一張桌子:

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];

…主鍵被另一個表引用。要對該表進行分區,我已經完成了以下步驟:

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


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

上述 2 個步驟成功完成,但是當我對錶進行分區時,我無法刪除主鍵聚集索引,因為它被其他表引用。我的動機是根據創建日期對錶條目進行分區。

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

源表和目標表必須具有相同的 FOREIGN KEY 約束。如果目標表有任何 FOREIGN KEY 約束,則源表必須在相應列上定義相同的外鍵,並且這些外鍵必須引用與目標表相同的主鍵。源表的外鍵不能標記為is_not_trusted(可在sys.foreign_keys目錄視圖中查看),除非目標表對應的外鍵也標記為is_not_trusted。 

因此,如果要對由外鍵引用的表進行分區,則兩個表需要具有相同的 FK。

同樣對於快速分區,以下規則適用:

你不能使用 fk 的

如果源表有主鍵,則不定義主鍵/外鍵。源表和源表保存主鍵的目標表之間不能存在活動的主鍵/外鍵關係。

如果目標表具有外鍵,則未定義主鍵/外鍵。源表和目標表保存外鍵的目標表之間不能有活動的主鍵/外鍵關係。

其他表中的任何外鍵都不能引用源表。源表不能被另一個表中的外鍵引用。

因此,您必須刪除約束,對錶進行分區並重新創建 costraint。最好作為一個事務來做,所以成功時可以送出,失敗時可以回滾。

可以在以下位置找到分步教程:http ://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/ 和

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

創建一個根據需要進行分區的新表,並將舊表中的數據複製到其中,而不是嘗試將新的分區方案應用於現有表。

複製數據後,刪除與舊表的關係,刪除它,然後重命名新表以代替舊表並重新創建所有關係。為了安全起見,在事務中執行此操作。

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

引用自:https://dba.stackexchange.com/questions/49483