Cannot insert duplicate key row in object with unique index . The duplicate key value. The statement has been terminated

Question

I am new to Outsystems and SQL. I am try to create a Bus Application where the entities are

When I try to create a new rider with the same name and different Route and bus Id. I get Cannot insert duplicate key row in object 'dbo.OSUSR_6SL_RIDER' with unique index 'OSIDX_OSUSR_6SL_RIDER_4NAME'. The duplicate key value is (ABC). The statement has been terminated. When I check Name field in the database table 'dbo.OSUSR_6SL_RIDER' it is not having the unique identifier set up. Can anybody please help me with this.

MSFT sql server ? Your output show `dbo`, your tags in your profile are MSFT related, yet this is marked `mysql` — Drew, Dec 17 '15 at 19:00
I would suggest showing the text output of the table schema definition, and the insert/update that caused this. You will note that by looking at your pre-existing data how obvious this is to debug. In short, pictures don't help and your data is getting in the way. — Drew, Dec 17 '15 at 19:09
Looking at your diagram, you I think you are limiting a rider to only one bus at a time. For a rider to be on many buses, you will have to duplicate all of rider data. Not a good design decision. This is a many-to-many relationship - a rider can take many buses, a bus can have many riders. — Raj More, Dec 17 '15 at 19:12

score 6 · Accepted Answer · answered Dec 17 '15 at 19:14

6

Open the Indexes tree under your table. You will find an Index named 'OSIDX_OSUSR_6SL_RIDER_4NAME'.

Script out that Index and you will see that it is a UNIQUE index on a "name" column that you are trying to create a duplicate value in.

You must either change that Index to include Route and Bus ID, or you must abandon your attempt to create a new row with a duplicate name.

answered Dec 17 '15 at 19:14

Tab Alleman

31,483
7
36
52

Thanks. how do I change the unique index that has been set on the filed name. – trx Dec 17 '15 at 20:21
Technically, you can't ALTER (change) an Index. You have to drop it and then re-create it. – Tab Alleman Dec 17 '15 at 20:25

score 0 · Answer 2 · answered Dec 17 '15 at 18:54

0

It looks like you are are creating an exact duplicate, i.e. a record with the same Id value. The index name it refers to seems to be auto generated by the db system. Therefor it is not necessarily referring to the Name field. Have a look at your indexes and look at the fields they contain. I wouldn't be surprised if OSIDX_OSUSR_6SL_RIDER_4NAME contains the Id field.

answered Dec 17 '15 at 18:54

C. van Dorsten

152
7

When I give the different name in the form and create. I works but with the same name and different Bus or Route it does show me the error. – trx Dec 17 '15 at 19:07

score 0 · Answer 3 · answered Dec 18 '15 at 03:06

If you are using the OutSystems platform, all the database management is done/generated when you publish from Service Studio, so it isn’t advisable to manipulate the database directly: you’re setting yourself up for a lot of maintenance pain and inconsistencies between different environments.

Double-click on the Entity Rider and it’ll open the edit window of your entity. In the Indexes tab you can define and change your indexes (unique or not) and the tool will (re)generate all the needed SQL commands.

See OutSystems Platform 9 Help | Indexes Tab for more details:

Cannot insert duplicate key row in object with unique index . The duplicate key value. The statement has been terminated

3 Answers3

Linked