Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm trying to rename a column of a table. I have a lot of tables with the word " couleur " and I renamed "manually" to " bulle ".

I've successfully renamed main_groupecouleurs to main_groupebulles . Now i'm working on main_groupe . I'm trying to rename groupe_couleurs_id to groupe_bulles_id

The SQL is quite self-explaining:

BEGIN TRANSACTION;
DROP INDEX main_groupe_fc5cee5b;
CREATE TABLE main_groupe7e12
    id INTEGER PRIMARY KEY NOT NULL,
    description TEXT NOT NULL,
    exemple TEXT,
    groupe_bulles_id INTEGER DEFAULT NULL,
    reference TEXT,
    FOREIGN KEY (groupe_bulles_id) REFERENCES main_groupebulles(id)
        DEFERRABLE INITIALLY DEFERRED
CREATE UNIQUE INDEX main_groupe_fc5cee5b ON main_groupe7e12 (groupe_bulles_id);
INSERT INTO main_groupe7e12(id, description, exemple, groupe_bulles_id, reference)
                     SELECT id, description, exemple, groupe_couleurs_id, reference
                     FROM main_groupe;
DROP TABLE main_groupe;
ALTER TABLE main_groupe7e12 RENAME TO main_groupe;
COMMIT;
When I run it, I get:
[SQLITE_CONSTRAINT]  Abort due to constraint violation
    (UNIQUE constraint failed: main_groupe7e12.groupe_bulles_id)
This means (I think I'm wrong here but I dont know what I'm missing) that it tries to insert some groupe_couleurs_id that are not in the referring table (= main_groupebulles). Thus I tried to see in the original table the problem:
SELECT * FROM main_groupe WHERE groupe_couleurs_id NOT IN (
    SELECT id FROM main_groupebulles
I got no rows! What am I missing?
                Ok I got your question, but my original table is filled with more that 500 rows which all refer to 12 "groupe_couleurs" so the groupe_couleurs_id index should not be unique, otherwise it would not have been possible to insert all those rows... and the SQL you see in my question comes from the "right click->modify table" of my PyCharm program... unless i'm missing something?
– Olivier Pons
                Nov 30, 2015 at 9:16
                Use something like select groupe_couleurs_id from main_groupe group by groupe_couleurs_id having count(*) > 1; to find the dupes.
– laalto
                Nov 30, 2015 at 9:17
                I've removed the word "unique" and it worked, thank you, may I ask you to answer below so I could check your answer as valid?
– Olivier Pons
                Nov 30, 2015 at 9:20
You have an UNIQUE index on your groupe_bulles_id column but based on the comments, there are a lot of valid duplicate values for that column coming from main_groupe.groupe_couleus_id and that causes the constraint violation.
Since having duplicate values is what you want, remove the UNIQUE from the CREATE UNIQUE INDEX ....
                I just dont understand how this could be possible... I guess that SQLite doesnt handle a lot of constraints.... like unique and I guess ON DELETE CASCADE and all basic stuff like that...
– Olivier Pons
                Nov 30, 2015 at 9:36
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.