CODEWITHSUNDEEP
sqlsql-servert-sqlconstraintsdefault
AtlasBowler
AtlasBowler

Reputation: 267

Default Constraint causing an error

I have the following SQL to create a table, but the "DEFAULT" in the first CONSTRAINT is giving me an error: "A Default constraint can exist only at the column level in a CREATE or ALTER TABLE statement."

I've never used default before so I have done some looking into this with internet research, but nothing has helped me solve the error yet or even really explained it to me.

CREATE TABLE [RuleEngine].[NCCIImportHistory](
                [NCCIImportHistoryID] [int] IDENTITY(1,1) NOT NULL,
                [StartTime] [datetimeoffset](7) NOT NULL,
                [EndTime] [datetimeoffset](7) NOT NULL,
                [CreatedOn] [datetimeoffset](7) NOT NULL,
                [CreatedBy_UserID] [int] NOT NULL,
CONSTRAINT [DF_NCCIImportHistory_CreatedOn]  DEFAULT (getutcdate()) FOR [CreatedOn],
CONSTRAINT [FK_NCCIImportHistory_User_CreatedBy] FOREIGN KEY([CreatedBy_UserID]) REFERENCES [Security].[User] ([UserID]),
CONSTRAINT [PK_NCCIImportHistoryID] PRIMARY KEY CLUSTERED ([NCCIImportHistoryID] ASC) 
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]

Upvotes: 2

Views: 1784

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try this

CREATE TABLE [RuleEngine].[NCCIImportHistory]
    (
      [NCCIImportHistoryID] [INT] IDENTITY(1, 1)
                                  NOT NULL ,
      [StartTime] [DATETIMEOFFSET](7) NOT NULL ,
      [EndTime] [DATETIMEOFFSET](7) NOT NULL ,
      [CreatedOn] [DATETIMEOFFSET](7) NOT NULL CONSTRAINT [DF_NCCIImportHistory_CreatedOn] DEFAULT ( GETUTCDATE() ),
      [CreatedBy_UserID] [INT] NOT NULL ,
      CONSTRAINT [FK_NCCIImportHistory_User_CreatedBy] FOREIGN KEY ( [CreatedBy_UserID] ) REFERENCES [Security].[User] ( [UserID] ) ,
      CONSTRAINT [PK_NCCIImportHistoryID] PRIMARY KEY CLUSTERED
        ( [NCCIImportHistoryID] ASC )

        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 ) ON [PRIMARY]
    )
ON  [PRIMARY]

or first create table and then constraints like this

CREATE TABLE [RuleEngine].[NCCIImportHistory]
    (
      [NCCIImportHistoryID] [INT] IDENTITY(1, 1) NOT NULL ,
      [StartTime] [DATETIMEOFFSET](7) NOT NULL ,
      [EndTime] [DATETIMEOFFSET](7) NOT NULL ,
      [CreatedOn] [DATETIMEOFFSET](7) NOT NULL,
      [CreatedBy_UserID] [INT] NOT NULL 
    )
ON  [PRIMARY]
GO


ALTER TABLE  [RuleEngine].[NCCIImportHistory] ADD CONSTRAINT [DF_NCCIImportHistory_CreatedOn] DEFAULT( GETUTCDATE() ) FOR [CreatedOn]
GO

ALTER TABLE  [RuleEngine].[NCCIImportHistory] ADD CONSTRAINT [FK_NCCIImportHistory_User_CreatedBy]  FOREIGN KEY ( [CreatedBy_UserID] ) REFERENCES [Security].[User] ( [UserID] )
GO

ALTER TABLE  [RuleEngine].[NCCIImportHistory] ADD CONSTRAINT [PK_NCCIImportHistoryID] PRIMARY KEY CLUSTERED( [NCCIImportHistoryID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 ) ON [PRIMARY]
GO

Upvotes: 0

jpw
jpw

Reputation: 44871

You didn't specify what database you are using, but based on syntax I think it's MS SQL Server. If so you can add the constraint inline as part of the column definition instead, like this:

[CreatedOn] [datetimeoffset](7) NOT NULL CONSTRAINT [DF_NCCIImportHistory_CreatedOn] DEFAULT (getutcdate()),

Upvotes: 3

Related Questions