I have some historised tables where columns are set to be NULLABLE by the AC, but i would like to have them as NOT NULLABLE
I would not use a pre- or post script because this will not solve the issue. The automatically generated code of the historicization depends on the properties of the source column. I think the best way is to change the properties of the source column.
The structure of the historicization table and the automatically generated code of the historicization procedure depend on the data source of the historicization. You can use a transformation as a data source and change the definition of the specific column to make it not nullable. Have a look in the SSMS if the source column is NULLABLE or not. If the column is marked as nullable then you can use a column transformation to make it explicitly "not nullable", for example
COALESCE(@this, 0) in case the column source is a number data type. Create the transformation and check if it is still marked as NULLABLE or not.
If you need the opposite way, you can use some
NULLIF(@this, 0) which will make this column as NULLABLE. (
@this is a placeholder for the current columns source.)
In your case:
You convert into varchar(7) and you will truncate the right part: '2020-09-30' will be '2020-09'.
You could try
COALESCE(CONVERT(varchar(7), startTime, 23),'') or some other default like
COALESCE(CONVERT(varchar(7), startTime, 23),'1900-01') depending on your required default.
In General: If your source is a table but not a transformation then first create a transformation which exactly repeats the tables structure, then you can switch the source of the historicization (AC will check if the columns and maybe data types are the same and will let you select only these new sources. But there are other ways to change).
Can i change the primary key of a hist table
Do you want to change the name of the default
Satz_ID? It is possible to change, for example my default is
Hist_ID. But I change this always at the beginning of a project. I don't know what happens in an existing project. Maybe it's also possible but all historicizations needs to be recompiled.
Look at this article in the wiki
Or do you mean something different?