none
Msg 156, Level 15, State 1, Line 19,Incorrect syntax near the keyword 'IDENTITY'.

    Question

  • Here is a Database script I wrote: I can't figure out what the problem is when I declare my primary Keys in the second and third table.

    Thanks in Advance

    Errors:

    Msg 156, Level 15, State 1, Line 19

    Incorrect syntax near the keyword 'IDENTITY'.

    Msg 156, Level 15, State 1, Line 29

    Incorrect syntax near the keyword 'IDENTITY'.

     

     

    CREATE DATABASE GoGreen

    Go

     

    USE GoGreen

    Create TABLE Student

    (StudentID INT NOT NULL IDENTITY PRIMARY KEY,   <---no Error here

    DrivePassCode char(3) NOT NULL,

    LName varchar(50) NOT NULL,

    FName varchar(50) NOT NULL,

    Address1 varchar(50) NOT NULL,

    Address2 varchar(50) NULL,

    City varchar(50) NOT NULL,

    PostalCode varchar(20) NOT NULL,

    PhoneNumber varchar(50) NOT NULL,

    Email varchar(50) NOT NULL,

    Smoker char(2) NOT NULL,

    NearestCityOrTown varchar(50) NOT NULL,

    PaymentType varchar(20) NOT NULL,)

     

    CREATE TABLE CoordinatedRideShare

    (RideID INT NOT NULL, IDENTITY PRIMARY KEY,    <---Error #1

    StudentID INT NOT NULL REFERENCES Student(StudentID)

    [ON DELETE {CASCADE}]

    [ON UPDATE {CASCADE}],

    M-W ARRIVAL TIME smalldatetime NOT NULL,

    M-W DEPART TIME smalldatetime NOT NULL,

    T-TH ARRIVAL TIME smalldatetime NOT NULL,

    T-TH DEPART TIME smalldatetime NOT NULL,)

     

    CREATE TABLE MeetingLocation

    (LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER,   <---Error#2

    RideID INT NOT NULL REFERENCES Coordinated RideShare(RideID)

    [ON DELETE {CASCADE}]

    [ON UPDATE {CASCADE}],

    House Pickup char(1) NULL,

    General meeting place varchar(50) NULL,)

     

    CREATE INDEX DrivePass

    ON Student (DrivePass)

    CREATE INDEX NearestCityOrTown

    ON Sudent (NearestCityOrTown)

     

    Friday, February 29, 2008 8:13 AM

Answers

  • hi,

     

    CREATE TABLE CoordinatedRideShare

    (RideID INT NOT NULL, IDENTITY PRIMARY KEY,    <---Error #1

     

    CREATE TABLE MeetingLocation

    (LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER,   <---Error#2

     

     

    there's a comma (,) before the IDENTITY keyword..

    regards

    Friday, February 29, 2008 9:09 PM

All replies

  • Hi,

     

    Code Snippet

    CREATE TABLE [dbo].[Student](

    [StudentID] [int] IDENTITY(1,1) NOT NULL,

    [DrivePassCode] [char](3) NOT NULL,

    [LName] [varchar](50) NOT NULL,

    [FName] [varchar](50) NOT NULL,

    [Address1] [varchar](50) NOT NULL,

    [Address2] [varchar](50) NULL,

    [City] [varchar](50) NOT NULL,

    [PostalCode] [varchar](20) NOT NULL,

    [PhoneNumber] [varchar](50) NOT NULL,

    [Email] [varchar](50) NOT NULL,

    [Smoker] [char](2) NOT NULL,

    [NearestCityOrTown] [varchar](50) NOT NULL,

    [PaymentType] [varchar](20) NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [StudentID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[CoordinatedRideShare](

    [RideID] [int] IDENTITY(1,1) NOT NULL,

    [StudentID] [int] NOT NULL,

    [M-W ARRIVAL TIME] [smalldatetime] NOT NULL,

    [M-W DEPART TIME] [smalldatetime] NOT NULL,

    [T-TH ARRIVAL TIME] [smalldatetime] NOT NULL,

    [T-TH DEPART TIME] [smalldatetime] NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [RideID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[MeetingLocation](

    [LocationID] [int] IDENTITY(1,1) NOT NULL,

    [RideID] [int] NOT NULL,

    [House Pickup] [char](1) NULL,

    [General meeting place] [varchar](50) NULL,

    PRIMARY KEY CLUSTERED

    (

    [LocationID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[CoordinatedRideShare] WITH CHECK ADD FOREIGN KEY([StudentID])

    REFERENCES [dbo].[Student] ([StudentID])

    ON UPDATE CASCADE

    ON DELETE CASCADE

    GO

    ALTER TABLE [dbo].[MeetingLocation] WITH CHECK ADD FOREIGN KEY([RideID])

    REFERENCES [dbo].[CoordinatedRideShare] ([RideID])

    ON UPDATE CASCADE

    ON DELETE CASCADE

    GO

     

     

     

    CU
    tosc

    Friday, February 29, 2008 10:17 AM
  •  

    Thank you for the assistance. The script you provided looks like it was created using SQL Server 2005 Managament Studio.  Am I correct, or do I just need to let my DDL improve as I learn more advanced aspects.

     

    My experience with SQL Server 2005:

     3 months writing SQL Queries

     1 month studying DDL

     

     

    Thanks again for your help,

    Friday, February 29, 2008 6:47 PM
  • hi,

     

    CREATE TABLE CoordinatedRideShare

    (RideID INT NOT NULL, IDENTITY PRIMARY KEY,    <---Error #1

     

    CREATE TABLE MeetingLocation

    (LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER,   <---Error#2

     

     

    there's a comma (,) before the IDENTITY keyword..

    regards

    Friday, February 29, 2008 9:09 PM
  • hi,

    more regarding [MeetingLocation] DDL...

    you have to "split" the constraint creation from the column's DDL definition if you provide a name for the constraint, like

    SET NOCOUNT ON;
    USE tempdb;
    GO
    PRINT 'error';
    CREATE TABLE dbo.t (
     Id int NOT NULL IDENTITY PRIMARY KEY autonumber
     --...
     );
    GO
    PRINT 'works..'
    CREATE TABLE dbo.t (
     Id int NOT NULL IDENTITY 
     --...
     , CONSTRAINT autonumber
          PRIMARY KEY (id)
     );
    GO
    DROP TABLE dbo.t;

     

    regards

    Friday, February 29, 2008 9:33 PM
  •  Andrea Montanari wrote:

    hi,

     

    CREATE TABLE CoordinatedRideShare

    (RideID INT NOT NULL, IDENTITY PRIMARY KEY,    <---Error #1

     

    CREATE TABLE MeetingLocation

    (LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER,   <---Error#2

     

     

    there's a comma (,) before the IDENTITY keyword..

    regards

     

    Error #3

     

    Autonumber is not a T-SQL keyword.

    Saturday, March 01, 2008 12:59 AM
  • Hi,


    I am having the same error .please help

    Msg 156, Level 15, State 1, Line 5 , Incorrect syntax near the keyword 'Where'

    SELECT A, B, "-2" AS C
              FROM (SELECT A, B,count(*) as total
                           FROM dbo.important
                                  group by A,B)
                                            WHERE total>1;
    Monday, April 27, 2009 5:57 PM
  • Hi,


    I am having the same error .please help

    Msg 156, Level 15, State 1, Line 5 , Incorrect syntax near the keyword 'Where'

    SELECT A, B, "-2" AS C
              FROM (SELECT A, B,count(*) as total
                           FROM dbo.important
                                  group by A,B)
                                            WHERE total>1;

    You need to give your subquery an alias.

    SELECT A, B, "-2" AS C
              FROM (SELECT A, B,count(*) as total
                           FROM dbo.important
                                  group by A,B) tableA
                                            WHERE total>1;

    Wednesday, June 03, 2009 8:06 PM
  • Hi I have the same issue...thanks for any help!

    linked server to oracle from sql2005...trying to not rec this error..."Error converting data type DBTYPE_DBTIMESTAMP to datetime".


    select * from openquery([ASTON-QUANTUMP],'select char([DELIVERY_DATE]) from qctl.so_detail'
    where [DELIVERY_DATE] is not null);

    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'where'.

    Also, trying ...want to to have the value of ''01/01/1753'' insert for invalid dates that are not recoginized by SQL?

    SELECT [DELIVERY_DATE]
    FROM OPENQUERY([ASTON-QUANTUMP],'SELECT DECODE ([DELIVERY_DATE],
    GREATEST([DELIVERY_DATE], TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), [DELIVERY_DATE], NULL)
    FROM QCTL.SO_DETAIL')
     
    OLE DB provider "OraOLEDB.Oracle" for linked server "ASTON-QUANTUMP" returned message "ORA-00936: missing expression".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT DECODE ([DELIVERY_DATE],
    GREATEST([DELIVERY_DATE], TO_DATE('01/01/1753','MM/DD/YYYY')), [DELIVERY_DATE], NULL)
    FROM QCTL.SO_DETAIL" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "ASTON-QUANTUMP".
    

    Thursday, August 27, 2009 5:44 PM