Incorrect syntax near the keyword 'unique'.

Question

Incorrect syntax near the keyword 'unique'.

Barcelona10

SSCertifiable

Points: 5372
More actions
June 29, 2020 at 6:08 pm

#3766257

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?

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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: 246960 More actions · Answer 2

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:

You should schema-qualify your object names
Your table should probably have a clustered primary key, unless you've deliberately created a heap for some reason?
As your COMMIT is within the TRY block, the BEGIN TRAN should also be within the TRY block.
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.
Mixing up singular and plural table names lacks consistency (if LOCATIONS is fine, why not FARE_TICKETS?)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Barcelona10 SSCertifiable Points: 5372 More actions · Answer 3

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.