ALTER TABLE [AKUZ].[T_CARD_NOTE_SIGNATURE] ADD [CardNoteFile] uniqueidentifier NULL
GO
update cns
set CardNoteFile = (select top 1 FileID from AKUZ.T_CARD_NOTE_FILE cnf where cnf.CardNote = cn.MedicalRecordID)
from AKUZ.T_CARD_NOTE_SIGNATURE cns join AKUZ.T_CARD_NOTE cn on cn.MedicalRecordID = cns.CardNote
GO
delete AKUZ.T_CARD_NOTE_SIGNATURE where CardNoteFile is null
ALTER TABLE [AKUZ].[T_CARD_NOTE_SIGNATURE] ALTER COLUMN [CardNoteFile] uniqueidentifier NOT NULL
GO
ALTER TABLE [AKUZ].[T_CARD_NOTE_SIGNATURE] ADD CONSTRAINT [FK_T_CARD_NOTE_FILE_T_CARD_NOTE_SIGNATURE_CardNoteFile]
FOREIGN KEY([CardNoteFile]) REFERENCES [AKUZ].[T_CARD_NOTE_FILE] ([FileID])
GO
ALTER TABLE [AKUZ].[T_CARD_NOTE_SIGNATURE] DROP CONSTRAINT [FK_T_CARD_NOTE_T_CARD_NOTE_SIGNATURE_CardNote]
GO
--indexes
DROP INDEX [IX_T_CARD_NOTE_SIGNATURE_CardNote] ON [AKUZ].[T_CARD_NOTE_SIGNATURE]
GO
CREATE NONCLUSTERED INDEX [IX_T_CARD_NOTE_SIGNATURE_CardNoteFile] ON [AKUZ].[T_CARD_NOTE_SIGNATURE]
(
	[CardNoteFile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
DROP INDEX [IX_T_CARD_NOTE_SIGNATURE_Post] ON [AKUZ].[T_CARD_NOTE_SIGNATURE]
GO
CREATE NONCLUSTERED INDEX [IX_T_CARD_NOTE_SIGNATURE_Post] ON [AKUZ].[T_CARD_NOTE_SIGNATURE]
(
	[Post] ASC,
	[Signed] ASC
)
INCLUDE([CardNoteFile],[SignType]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
DROP INDEX [IX_T_CARD_NOTE_SIGNATURE_RoleCode] ON [AKUZ].[T_CARD_NOTE_SIGNATURE]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_T_CARD_NOTE_SIGNATURE_RoleCode] ON [AKUZ].[T_CARD_NOTE_SIGNATURE]
(
	[RoleCode] ASC,
	[SignType] ASC
)
INCLUDE([CardNoteFile]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
--endIndexes
ALTER TABLE [AKUZ].[T_CARD_NOTE_SIGNATURE] DROP COLUMN [CardNote]
GO
