应用DEFAULT约束报错：SQL语句中FOR附近存在语法错误咨询

Let's break down why you're hitting that syntax error and how to fix it quickly.

The Root Cause #

Your current attempt uses table-level DEFAULT constraints with the FOR keyword, which should work in SQL Server—but it looks like the combination of NOT NULL and table-level constraints might be tripping things up, or you might be mixing syntax from other databases (like MySQL, which doesn't use FOR for defaults).

A simpler, more reliable approach is to define the DEFAULT constraints directly at the column level instead of using table-level constraints. This is the most common and readable way to set defaults in SQL Server.

Corrected SQL Statement #

Here's the fixed version using column-level defaults:

CREATE TABLE Test (
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    CreatedBy VARCHAR(20) NOT NULL DEFAULT USER_NAME(),
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    UpdatedBy VARCHAR(20) NOT NULL DEFAULT USER_NAME(),
    LastUpdated DATETIME NOT NULL DEFAULT GETDATE()
)
GO

If You Prefer Table-Level Constraints #

If you specifically need table-level constraints (for better organization in larger tables), here's the correct syntax that avoids the error:

CREATE TABLE Test (
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    CreatedBy VARCHAR(20) NOT NULL,
    CreatedDate DATETIME NOT NULL,
    UpdatedBy VARCHAR(20) NOT NULL,
    LastUpdated DATETIME NOT NULL,
    CONSTRAINT DF_Test_CreatedBy DEFAULT USER_NAME() FOR CreatedBy,
    CONSTRAINT DF_Test_CreatedDate DEFAULT GETDATE() FOR CreatedDate,
    CONSTRAINT DF_Test_UpdatedBy DEFAULT USER_NAME() FOR UpdatedBy,
    CONSTRAINT DF_Test_LastUpdated DEFAULT GETDATE() FOR LastUpdated
)
GO

(Note: Using DF_ as a prefix for default constraints is a common naming convention to make them easier to identify.)

Why This Works #

• Column-level defaults are directly attached to the column definition, eliminating the need for the FOR keyword and simplifying the syntax.
• Table-level constraints require the FOR keyword to specify which column the default applies to, but ensuring your naming convention is clear and the syntax is exact (no typos) is key. The error you saw was likely due to a subtle syntax quirk or version-specific behavior, but switching to column-level defaults avoids that entirely.

内容的提问来源于stack exchange，提问作者Subramonian Inian