Altered PolicyId in tblPolicyInsuree from null to not null

hirensoni913 · hirensoni913 · commit 1bfdc3588c12 · 2022-07-25T14:43:36.000+03:00
diff --git a/sql/base/1_schema_tables.sql b/sql/base/1_schema_tables.sql
@@ -1453,7 +1453,7 @@ GO
 CREATE TABLE [dbo].[tblInsureePolicy](
 	[InsureePolicyId] [int] IDENTITY(1,1) NOT NULL,
 	[InsureeId] [int] NOT NULL,
-	[PolicyId] [int] NULL,
+	[PolicyId] [int] NOT NULL,
 	[EnrollmentDate] [date] NULL,
 	[StartDate] [date] NULL,
 	[EffectiveDate] [date] NULL,
diff --git a/sql/migrations/1_migration_latest.sql b/sql/migrations/1_migration_latest.sql
@@ -3411,6 +3411,15 @@ GO
 DROP INDEX [tblInsureePolicy_ValidityTo_EffectiveDate_ExpiryDate] ON [dbo].[tblInsureePolicy]
 GO
 
+DROP INDEX [missing_index_203] ON [dbo].[tblInsureePolicy]
+GO
+
+DROP INDEX [missing_index_356] ON [dbo].[tblInsureePolicy]
+GO
+
+DROP INDEX [NCI_tblInsureePolicy_PolicyID] ON [dbo].[tblInsureePolicy]
+GO
+
 --Delete all dirty data where InsureeId is null
 DELETE FROM tblInsureePolicy WHERE InsureeId IS NULL
 GO
@@ -3420,6 +3429,11 @@ ALTER TABLE tblInsureePolicy
 ALTER COLUMN InsureeId INT NOT NULL
 GO
 
+IF COL_LENGTH(N'tblInsureePolicy', N'PolicyId') IS NOT NULL
+ALTER TABLE tblInsureePolicy 
+ALTER COLUMN PolicyId INT NOT NULL
+GO
+
 CREATE NONCLUSTERED INDEX [missing_index_181] ON [dbo].[tblInsureePolicy]
 (
 	[InsureeId] ASC,
@@ -3451,3 +3465,26 @@ CREATE NONCLUSTERED INDEX [tblInsureePolicy_ValidityTo_EffectiveDate_ExpiryDate]
 )
 INCLUDE([InsureeId],[PolicyId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
 GO
+
+CREATE NONCLUSTERED INDEX [missing_index_203] ON [dbo].[tblInsureePolicy]
+(
+	[EffectiveDate] ASC,
+	[ValidityTo] ASC
+)
+INCLUDE([PolicyId]) 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 = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
+GO
+
+CREATE NONCLUSTERED INDEX [missing_index_356] ON [dbo].[tblInsureePolicy]
+(
+	[PolicyId] ASC,
+	[ValidityTo] ASC,
+	[EffectiveDate] ASC,
+	[ExpiryDate] 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 = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
+GO
+
+CREATE NONCLUSTERED INDEX [NCI_tblInsureePolicy_PolicyID] ON [dbo].[tblInsureePolicy]
+(
+	[PolicyId] 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 = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
+GO
