Incorrect syntax near the keyword 'unique'.

  • Barcelona10

    SSCertifiable

    Points: 5055

    Hi All

    I am trying to create new table on sql server 2014 enterprise edition with constraint and unique non-cluster index and getting  "Incorrect syntax near the keyword 'unique'." msg. It runs ok on sql 2017 server.I tried to look into books online but link takes to 2019.here is my script

    EGIN TRAN

    BEGIN try

    if not exists(select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'TICKET_LOCATION')

    begin

    create table TICKET_LOCATION

    (

    FARE_TICKET_ID tinyint not null

    , LOCATION_ID int not null

    , index UQ_TICKET_LOCATION unique nonclustered (FARE_TICKET_ID, LOCATION_ID)

    , constraint FK_CL_TICKET_ID foreign key (FARE_TICKET_ID) references FARE_TICKET (FARE_TICKET_ID)

    , constraint FK_CL_LOCATION_ID foreign key (LOCATION_ID) references LOCATIONS (LOCATION_ID)

    );

    print N'TICKET_LOCATION table added';

    end

    else

    print N'TICKET_LOCATION table already exists';

    commit;

    END TRY

    begin CATCH

    ROLLBACK;

    PRINT 'TICKET_LOCATION Failed';

    SELECT

    ERROR_NUMBER() AS ErrorNumber

    ,ERROR_SEVERITY() AS ErrorSeverity

    ,ERROR_STATE() AS ErrorState

    ,ERROR_LINE() AS ErrorLine

    ,ERROR_MESSAGE() AS ErrorMessage;

    END CATCH

     

    Msg 156, Level 15, State 1, Line 10

    Incorrect syntax near the keyword 'unique'.

     

    Any Idea?

     

  • pietlinden

    SSC Guru

    Points: 62811

    This works... you need a GO statement between your CREATE and INSERT statements if you're going to run a single script, because they have to be in different batches.

    use tempdb;
    GO

    create table TICKET_LOCATION
    (
    FARE_TICKET_ID tinyint not null
    , LOCATION_ID int not null
    , index UQ_TICKET_LOCATION unique nonclustered (FARE_TICKET_ID, LOCATION_ID)
    );
    GO

    INSERT INTO TICKET_LOCATION VALUES (1,1),(1,2),(2,1),(2,2);

    -- should fail, and it does (unique index violation).
    INSERT INTO TICKET_LOCATION VALUES (1,1);
  • Phil Parkin

    SSC Guru

    Points: 244589

    Try doing it in two stages:

            CREATE TABLE TICKET_LOCATION
    (
    FARE_TICKET_ID TINYINT NOT NULL
    ,LOCATION_ID INT NOT NULL
    ,CONSTRAINT FK_CL_TICKET_ID
    FOREIGN KEY (FARE_TICKET_ID)
    REFERENCES FARE_TICKET (FARE_TICKET_ID)
    ,CONSTRAINT FK_CL_LOCATION_ID
    FOREIGN KEY (LOCATION_ID)
    REFERENCES LOCATIONS (LOCATION_ID)
    );

    CREATE UNIQUE NONCLUSTERED INDEX UQ_TICKET_LOCATION
    ON dbo.TICKET_LOCATION (
    FARE_TICKET_ID
    ,LOCATION_ID
    );

    I have the following comments about your code:

    1. You should schema-qualify your object names
    2. Your table should probably have a clustered primary key, unless you've deliberately created a heap for some reason?
    3. As your COMMIT is within the TRY block, the BEGIN TRAN should also be within the TRY block.
    4. You should include a THROW within your CATCH block, otherwise the details of the error will merely be SELECTED and not reported as an error.
    5. Mixing up singular and plural table names lacks consistency (if LOCATIONS is fine, why not FARE_TICKETS?)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Barcelona10

    SSCertifiable

    Points: 5055

    doing it in two stages works.I guess this specific t-sql  has comparability issue between 2014 and 2017 versions of sql server

    Thank you guys.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply