I am working in 2022R1.
I created a custom table with a default to one of the fields. I created the table in MSSQL Management Studio. After I create the table and create a script of the table it shows that the default is there. I tested it by adding a dummy record and “AV” is filled into the Module field.
This is the script to create the table:
CREATE TABLE [dbo].[ICSMergeLock](
[CompanyID] [int] NOT NULL,
[Module] [char](2) NOT NULL,
CONSTRAINT [ICSMergeLock_PK] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ICSMergeLock] ADD DEFAULT ((0)) FOR [CompanyID]
GO
ALTER TABLE [dbo].[ICSMergeLock] ADD DEFAULT ('AV') FOR [Module]
GO
In the project editor, I ADD CUSTOM TABLE SCHEMA for the table. In Edit Project Items, this is what the project is storing for this table:
<Sql TableName="ICSMergeLock" TableSchemaXml="#CDATA">
<CDATA name="TableSchemaXml"><![CDATA[<table name="ICSMergeLock">
<col name="CompanyID" type="Int" default="Zero" />
<col name="Module" type="Char(2)" raw-default="'AV'" />
<index name="ICSMergeLock_PK" clustered="true" primary="true" unique="true">
<col name="CompanyID" />
</index>
</table>]]></CDATA>
</Sql>
Using a clean instance, I publish the project. This is the table it creates.
CREATE TABLE [dbo].[ICSMergeLock](
[CompanyID] [int] NOT NULL,
[Module] [char](2) NOT NULL,
CONSTRAINT [ICSMergeLock_PK] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ICSMergeLock] ADD DEFAULT ((0)) FOR [CompanyID]
GO
Note that the command to create the AV default for the Module field is missing.
I can add this as a separate SQL script, but why is the publish process not creating my default?
