Search code examples
sqlazuredatetimedefaultazure-synapse

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

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.enter image description here

Solution

  • 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;