0

For my scenario I have already created a database user id that is auto incremented and they have already put their company id number in the registration. In the registration table both inputs are stored, but I would like to combine both together in a (database_id) (user_id) + "_" + (company_id) form so that everyone who has signed up for the application is unique in the database. Would this be possible?

3
  • 3
    That leaves a lot open. For starters please post the table definition as CREATE TABLE statement and the version of MySQL you're using. Also, what is the database_id meant to be? Is this for some multitenant application?
    – sticky bit
    Jun 30, 2018 at 18:28
  • database_id is the id I am assigning to all users that sign up for the application I am creating. It is auto incremented starting at 1 so each user is given a number, but also I want to combine that with the company id that's already assigned to them. Also this version is 6.3.10
    – wonderhead
    Jun 30, 2018 at 18:35
  • OK, so its actually a user ID and a company ID (misleading names you chose). 6.3.10 is rather the version of MySQL Workbench, I guess. But the client isn't important here. I wanted to know the server version. And the tables' DDL are still missing.
    – sticky bit
    Jun 30, 2018 at 18:42

1 Answer 1

1

Well, I think the questions is not well formulated, but as far as I got it:

  • You have a table containing two columns user_id and company_id
  • You have set the user_id to be AUTO_INCREMENT (so it's a PRIMARY KEY)
  • You are willing to change this column to user_id + "_" + company_id to make it UNIQUE

Answer: you're doing it wrong

Both data must not be concatenated.

Instead, what you want is a (user_id, company_id) UNIQUE KEY, which can be done with an ALTER TABLE my_table ADD UNIQUE INDEX user_company (user_id, company_id).

Thus, the way you want your AUTO_INCREMENT to work is not clear. IMO, you should remove it from the user_id, and add it on a new dedicated column id. Then, when you INSERT/UPDATE in that table, you'll have to know the user_id you're inserting/updating, which makes sense since that user_id is no longer UNIQUE to identify a row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.