OUTPUT 句で FK 付テーブルに INTO する時のメモ

SQL Server

OUTPUT 句 (Transact-SQL)
今日気付いたのですが、INTO 先のテーブルには FOREIGN KEY 付いてるとダメという制約がありました。

幾つかの回避方法があると思いますが、ここでは テーブル変数 に退避してからという方法のメモです。
※FK を一旦無効にしてから OUTPUT で INTO したあと再度 FK 有効にするってやり方もあります。

対処方法のメモ

create table [ユーザー] (
    [ID] int not null primary key, 
    [名前] nvarchar(10) not null 
)
create table [通知] (
    [ID] int not null primary key, 
    [種類] nvarchar(10) not null, 
    [内容] nvarchar(100)
)
create table [ユーザーへの通常通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)

insert into [ユーザー] ([ID], [名前])
values (1, N'一郎'), (2, N'二郎'), (3, N'三郎')

insert into [通知] ([ID], [種類], [内容])
values (1, N'定期', N'一郎さん。お元気ですか？')
, (2, N'定期', N'二郎さん。お元気ですか？')
, (3, N'定期', N'三郎さん。お元気ですか？')
, (4, N'アラート', N'三郎さん。不明な端末でログイン試行されました。')
, (5, N'セキュリティ', N'三郎さん。パスワードリセット要求。')
, (6, N'アラート', N'一郎さん。アカウントロックされました。')

insert into [ユーザーへの通常通知] ([ユーザーID], [通知ID], [日時])
values (1, 1, '2017-02-01 12:34:56')
, (2, 2, '2017-02-02 20:02:01')
, (3, 3, '2017-02-03 02:11:48')
, (3, 4, '2017-02-07 19:02:32')
, (3, 5, '2017-02-07 19:09:51')
, (1, 6, '2017-02-08 12:45:20')

select * from [ユーザー]
select * from [通知]
select * from [ユーザーへの通常通知]


ってのがあったときに、アラート通知の紐づけは別テーブルにしよう！と変更したとします。
テーブルを作って、

create table [ユーザーへのアラート通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)

データを移行します。

delete from [ユーザーへの通常通知]
output deleted.* into [ユーザーへのアラート通知] -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)

これがエラーになります。

メッセージ 332、レベル 16、状態 1、行 99
OUTPUT INTO 句の対象のテーブル 'ユーザーへのアラート通知' を、(主キー、外部キー) リレーションシップのどちらにも配置できません。参照制約 'FK__ユーザーへのアラー__ユーザーI__6477ECF3' が見つかりました。

FK があるとダメーと言われました。
というわけで、一旦テーブル変数に INTO してから移行します。

declare @t table (
    [ユーザーID] int not null, 
    [通知ID] int not null, 
    [日時] datetime2(0) not null
)
delete from [ユーザーへの通常通知]
output deleted.* into @t -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)
insert into [ユーザーへのアラート通知]
select * from @t

select * from [ユーザーへの通常通知]
select * from [ユーザーへのアラート通知]

出来上がり！

クエリ全文

create table [ユーザー] (
    [ID] int not null primary key, 
    [名前] nvarchar(10) not null 
)
create table [通知] (
    [ID] int not null primary key, 
    [種類] nvarchar(10) not null, 
    [内容] nvarchar(100)
)
create table [ユーザーへの通常通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)

insert into [ユーザー] ([ID], [名前])
values (1, N'一郎'), (2, N'二郎'), (3, N'三郎')

insert into [通知] ([ID], [種類], [内容])
values (1, N'定期', N'一郎さん。お元気ですか？')
, (2, N'定期', N'二郎さん。お元気ですか？')
, (3, N'定期', N'三郎さん。お元気ですか？')
, (4, N'アラート', N'三郎さん。不明な端末でログイン試行されました。')
, (5, N'セキュリティ', N'三郎さん。パスワードリセット要求。')
, (6, N'アラート', N'一郎さん。アカウントロックされました。')

insert into [ユーザーへの通常通知] ([ユーザーID], [通知ID], [日時])
values (1, 1, '2017-02-01 12:34:56')
, (2, 2, '2017-02-02 20:02:01')
, (3, 3, '2017-02-03 02:11:48')
, (3, 4, '2017-02-07 19:02:32')
, (3, 5, '2017-02-07 19:09:51')
, (1, 6, '2017-02-08 12:45:20')

select * from [ユーザー]
select * from [通知]
select * from [ユーザーへの通常通知]
go

create table [ユーザーへのアラート通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)
go

-- エラーになる！
delete from [ユーザーへの通常通知]
output deleted.* into [ユーザーへのアラート通知] -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)
go

declare @t table (
    [ユーザーID] int not null, 
    [通知ID] int not null, 
    [日時] datetime2(0) not null
)
delete from [ユーザーへの通常通知]
output deleted.* into @t -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)
insert into [ユーザーへのアラート通知]
select * from @t

select * from [ユーザーへの通常通知]
select * from [ユーザーへのアラート通知]
go

-- 後片付け
drop table [ユーザーへのアラート通知]
drop table [ユーザーへの通常通知]
drop table [通知]
drop table [ユーザー]