what is wrong with my create table SQL?

Question

Hello everyone,

I am using SQL Server 2008 management studio to execute the following SQL statements, and here is the related error message from SQL Server management studio. Any ideas what is wrong?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create TABLE [dbo].[BatchStatus](
    [BatchID] [uniqueidentifier] NOT NULL CONSTRAINT [PK_BatchStatus_ID],
    [BatchStatus] [int] NULL,
    CONSTRAINT [PK_BatchStatus_ID] PRIMARY KEY CLUSTERED 
    (
        [BatchID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO



Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table
expression, an xmlnamespaces clause or a change tracking context clause, the 
previous statement must be terminated with a semicolon.

thanks in advance, George

Answer 1

Try this:

Create TABLE [dbo].[BatchStatus](
    [BatchID] [uniqueidentifier] NOT NULL,
    [BatchStatus] [int] NULL,
    CONSTRAINT [PK_BatchStatus_ID] PRIMARY KEY CLUSTERED 
    (
        [BatchID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 2

You are defining the "PK_BatchStatus_ID" constraint twice - once on the line where you define the BatchID field, once at the end of the table definition.

You can EITHER define your constraint "inline" with the column:

CREATE TABLE [dbo].[BatchStatus]
    ([BatchID] [uniqueidentifier] NOT NULL 
        CONSTRAINT [PK_BatchStatus_ID] PRIMARY KEY,
    [BatchStatus] [int] NULL
) ON [PRIMARY]

or then you can define it AFTER all the columns of the table

CREATE TABLE [dbo].[BatchStatus]
    ([BatchID] [uniqueidentifier] NOT NULL,
    [BatchStatus] [int] NULL,
    CONSTRAINT [PK_BatchStatus_ID] PRIMARY KEY CLUSTERED 
    (
        [BatchID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

but you can't have both (partially)

Marc

Answer 3

Maybe this is what you really wanted. A constraint specifying the default value

Create TABLE [dbo].[BatchStatus](
    [BatchID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BatchStatus_ID] DEFAULT((0)),
    [BatchStatus] [int] NULL,
    CONSTRAINT [PK_BatchStatus_ID] PRIMARY KEY CLUSTERED 
    (
        [BatchID] ASC
    ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Answer 4

while using

USE mssqltips_tde; CREATE DATABASE ENCRYPTION KEY with ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert GO

getting error

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'KEY'. Msg 319, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

please help in resolving the same as i need to implement Encryption on my DB