Duplicate entry for column user, code error 1166

Hi guys,
I have three tables User, A, and B in my database.
The relations are:

    An element of A is related to an element of User with a 1:1 relation, by the object relation column A.user (UNIQUE and in Table A)

    An element of B is related to an element of User with a 1:1 relation, by the object relation column B.user (UNIQUE and in Table B)
    An element of A is related with one and only one element of B, while an element of B can be related with more than one element of A.

I would like to create a record in the table A. In order to do so I follow these steps:
    I create a Backendless user objetc and registering it in the Database. I retrieve an already existing B object from the Database, by its id. I create an A object filling it with both the registered user object, created above, and the retrieved B object.
When I try to save (insert) the object A, I get the following error:
Duplicate entry for column idUser, code error 1166

After some tries, it appears that the error is due to the UNIQUE column in B.user.
How can I insert my data in the Database, without removing the UNIQUE property of the column B.user?
How can I create a relation between two existing objects via Java code?
Thank you!

Hi Starscream,

Would it be possible for you run an export on your schema and email it to support@backendless.com? This way we can try duplicating this error using exactly the same table setup. Schema export is available at Manage > Export in Backendless console. When you send an email, please include a link to this support topic.

Regards,
Mark

Hi Mark,

I sent the email in the weekend.
Have you found what’s the problem?

Hello,

I recreated the backend, set the unique constraints in both table A (for column “idUser”) and table B (also for the column “idUser”). Here’s the code I run and it works every time. How is it different from what you’re doing? (ignore the fact that the calls are synchronous, it does not make any difference on the backend, just helps to keep the code more compact on the client-side)

BackendlessUser user = new BackendlessUser();
user.setEmail( UUID.randomUUID() + "@backendless.com" );
user.setPassword( "password" );
user = Backendless.UserService.register( user );
Chief chief = Backendless.Data.of( Chief.class ).findFirst();
Enterprise enterprise = new Enterprise();
enterprise.setCity( "Dallas" );
enterprise.setDescription( "Sample enterprise" );
enterprise.setAddress( "123 Main St" );
enterprise.setIdChief( chief );
enterprise.setIdUser( user );
enterprise.save();

Hi Mark,

I’m trying to do the same thing, here is the code:

//add user 
BackendlessUser user = new BackendlessUser(); 

user.setProperty("email", email); 

user.setPassword(password); 
 

JSONObject jsonObject = new JSONObject(); 

Boolean result = true; 

try { 
 

    user = Backendless.UserService.register(user); 
 
 

    if (user != null) { 
 

       Chief chief = Backendless.Persistence.of(Chief.class).findById(idChief);

           if (chief != null) { 
 

               //create enterprise 
 

               Enterprise enterprise = new Enterprise(); 
 

               // Mandatory fields 
 

               enterprise.setVat(vat); 
 

               enterprise.setName(name); 
 

               enterprise.setCity(city); 
 

               enterprise.setAddress(address); 
 

               enterprise.setZipCode(zipCode); 
 

               enterprise.setTaxCode(taxCode); 
 

               enterprise.setDistrict(district); 
 

               enterprise.setDescription(description); 
 

               enterprise.setTelephoneNumber(telephoneNumber); 
 

               enterprise.setIsOrderAcceptedByDefault(acceptOrderByDefault); 
 

               // Optional 
 

               enterprise.setFax(fax); 
 

               enterprise.setUser(user); 
 

               enterprise.setChief(chief); 
 

               enterprise.setLogoURL(logoURL); 
 

               enterprise.setWebsiteAddress(websiteAddress); 
 

               enterprise.save();

           } else { 
 

               jsonObject.append("errorMessage", "No chief found with the specified id"); 

               result = false; 
 

            } 
 

        } else { 
 

             jsonObject.append("errorMessage", "User registration failed."); 

             result = false; 
 

        } 

   } catch (BackendlessException exception) { 
 

         jsonObject.append("errorCode", exception.getCode()); 
 

        jsonObject.append("errorMessage", exception.getMessage() + " /// " + 


        exception.getDetail()); 
 

        result = false; 

     } 



     jsonObject.append("result", result);

But I get the same error as in the question title.

Could you test it if I send you my application ID?

Hi Mark,

I have an other question:
[center]Did you set the Chief.user column as UNIQUE in your test?
[/center]

Hi Mark,

I fixed my problem.

[SOLUTION]
It turn out that by resetting completely my application via the web console, everything works fine!

I tried to explain this behaviour to myself by thinking that something weird is going on behind Backendless, but I don’t know what exactly.
It seems that Backednless, or a module of him which works with persistency, keeps in memory (I think it is something like caching), all the records I create on my data tables. Therefore even if I delete a record, the systems keeps it in memory (without showing it to me), and when I try to set (via code) the same object to a column which has the UNIQUE attribute it gives me an error: “Duplicate entry for …”
Hence it seems to me that Backednless says something like this: “Hey, you cannot add and other object which is already in, on this column which must contain only UNIQUE objects.”

On the other hand, the error doesn’t appear if I relate the records via the console.

I hope there is a better explanation to this issue and I hope someone will find useful my solution!