An expression cannot be used with default constraints. Specify only constants for default constraints in azure synapse sql

Question

I want to add a default constraint using CREATE TABLE in AZURE SYNAPSE SQL, but I received the below error message instead. Also tried in alter statement but no luck. I have tried multiple ways but no luck. Could you please help me with the solution.

Answer 1

As per the documentation expressions (and functions like GETDATE()) cannot be used with defaults:

Note In Azure Synapse Analytics, only constants can be used for a default constraint. An expression cannot be used with a default constraint.

The solution is to specify the column as NOT NULL and ensure a date is supplied when the data is inserted into the table, eg:

INSERT INTO yourTable ( someId, yourDateColumn )
SELECT someId, GETDATE()
FROM someTable;

An alternative would be to use CREATE TABLE AS (CTAS) and specify the column value there, eg:

CREATE TABLE yourTargetTable
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)
AS 
SELECT someId, GETDATE() AS loadDate
FROM stagingTable;