CREATE TABLE [AKUZ].[T_CDA_DELIVERY_PLAN]
(
    [CdaDeliveryPlanID] uniqueidentifier NOT NULL,
    [DateCreationPlan] datetime NOT NULL,
    [CdaExaminationSpecialist] uniqueidentifier NULL,
    [TimeCreationPlan] varchar(255) NOT NULL,
    [EhrRusFrn430] uniqueidentifier NOT NULL
)
ON [PRIMARY]
GO

-- Alters

ALTER TABLE [AKUZ].[T_CDA_DELIVERY_PLAN] ADD CONSTRAINT [PK_T_CDA_DELIVERY_PLAN]
PRIMARY KEY CLUSTERED ([CdaDeliveryPlanID]) ON [PRIMARY]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_CDA_DELIVERY_PLAN]') AND name = N'UQ_T_CDA_DELIVERY_PLAN_CdaExaminationSpecialist' and is_unique_constraint <> 1)
DROP INDEX [UQ_T_CDA_DELIVERY_PLAN_CdaExaminationSpecialist] ON [AKUZ].[T_CDA_DELIVERY_PLAN]
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_CDA_DELIVERY_PLAN]') AND name = N'UQ_T_CDA_DELIVERY_PLAN_CdaExaminationSpecialist' and is_unique_constraint = 1)
ALTER TABLE [AKUZ].[T_CDA_DELIVERY_PLAN] DROP CONSTRAINT [UQ_T_CDA_DELIVERY_PLAN_CdaExaminationSpecialist]
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[AKUZ].[T_CDA_DELIVERY_PLAN]') AND name = N'CdaExaminationSpecialist_UQ')
ALTER TABLE [AKUZ].[T_CDA_DELIVERY_PLAN] DROP COLUMN [CdaExaminationSpecialist_UQ]
GO

ALTER TABLE [AKUZ].[T_CDA_DELIVERY_PLAN]ADD [CdaExaminationSpecialist_UQ] AS (CASE WHEN [CdaExaminationSpecialist] IS NOT NULL THEN NULL ELSE [CdaDeliveryPlanID] END)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_CDA_DELIVERY_PLAN_CdaExaminationSpecialist] ON [AKUZ].[T_CDA_DELIVERY_PLAN] ([CdaExaminationSpecialist] ASC, [CdaExaminationSpecialist_UQ] ASC) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_CDA_DELIVERY_PLAN] ADD CONSTRAINT [FK_T_CDA_EXAMINATION_SPECIALIST_T_CDA_DELIVERY_PLAN_CdaExaminationSpecialist]
FOREIGN KEY([CdaExaminationSpecialist]) REFERENCES [AKUZ].[T_CDA_EXAMINATION_SPECIALIST] ([CdaExaminationSpecialistID])
GO

ALTER TABLE [AKUZ].[T_CDA_DELIVERY_PLAN] ADD CONSTRAINT [FK_T_EHR_RUS_TREE_T_CDA_DELIVERY_PLAN_EhrRusFrn430]
FOREIGN KEY([EhrRusFrn430]) REFERENCES [EHR].[T_EHR_RUS_TREE] ([EhrRusID])
GO