I have a database with 10 tables, I'm using Postgresql db on pgAdmin4 (symfony back-end), I imported the data from 10 different csv files, in each data files i added an ID column to set their values for the single fact that there is foreign key, lets just say TABLE1 is a foreign key in TABLE_2, TABLE_2_ID is a foreign key in TABLE_3, TABLE_3_ID is a foreign key in TABLE_C etc... it goes on like this till the last table.
The data import for the csv files worked. I can display my data in my front-end. Now that I'm trying to insert new data into any table via the the plateform user interface, I'm getting a constraint error :
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "theme_pkey" DETAIL: Key (id)=(4) already exists."
Let just say I'm trying to create a new value in Table_2, Doctrine orm will launch an error that's going to say the id_key already exist, it seem like my database didn't update itself by following the current data I have already in the data base, like the ID isn't incremented.
I looked around it seems like it is a common when you import your database from an outside source, but can't manage to find something that would get me out of this error message and move forward with my dev. I look at everything they all talk about update sequence but nothing seems to fit my problem.
After importing my data from a csv file to my postrgresql database using PGAdmin4, the data was successfully imported, querying to see the data worked and each row had an id, in my mind everything was fine until i tried to insert in to my database a new object, but it seems like my database was stuck in Primary_Key id 1, it wasn't taking into account the database that was imported and the fact that it showed on the table an object with a an Id of 1. I didn't fully understand what was going on cause i'm not an expert with sql, the convention and restriction that applies to it, i knew enough to get things working around with using an ORM.
I came to the conclusion after hours of research and documentations reading that a thing called Sequence existed and that each table had their own sequence, for example tableA and tableB had a tableA_id_seq and a tableB_id_seq.
All i had to do was to get the max id from my table / get the next Id and simply increment that next Id.
---GETTING THE MAX VALUE ID FROM MY TARGETED TABLE
SELECT MAX(id) FROM table;
--- GETTING THE NEXT ID VALUE FROM THAT TARGETED TABLE
SELECT NEXT ID
SELECT NEXTVAL('table_id_seq');
Lets just say my max id value from my table was 90, so in theory my next value is suppose to be 91, but when i launched the 2nd script the next value was 1,
So i had to set the next value based on my max value + 1
---- SETTING NEXT VALUE THAT FOLLOWS MY MAX VALUE
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)+1)
Hope this helps out the next person that runs into similar a issue.