I have a table "Words" with primary key autoincrement. Another table "Translated" related to the table "Words" through the primary key of "Words" I need to delete some columns in the table "Words" but in sqlite this is not possible directly. So I have created a new table "Words2" without the unwanted columns and I want to transfer the relevant columns to my new table "Words2".
A few questions: How do you suggest this is done? Is it recommended to use autoincrement in a relational databse?
Best Regards Lars Frederiksen
There are many things here that I (and possibly others) do not understand.
What do you mean "not possible directly"? Any data an be deleted from any table directly. The only case I can think of is if there is a Foreign-Key relation with an ON DELETE RESTRICT constraint on the second table, in which case deleting the item first from the other table will work just fine, or indeed, simply switching off foreign-key checking.
What "unwanted columns" do you have? Do you mean you have made a copy of the "Words" table but then only copied some/one of the columns over? If so, why??
"I want to transfer the relevant columns..." there is no way to "transfer" columns in any RDBMS Database. What you would need is to make new columns and then update the data in them using UPDATE. You cannot use INSERT because the rows exist already.
I will answer what I think you might need, but if it is not the answer you were looking for, please clarify the points above.
Is it recommended to use autoincrement in a relational databse?
It is neither recommended, nor discouraged. Auto-incrementing in any RDBMS is a tool you can use to achieve automatic consecutive numbering in a unique integer primary key (or other integer keys in some other databases). Using it is a convenience and nothing more. Neither recommended nor shunned.
How do you suggest this is done?
Let me assume a primary table "Words" with schemata like this:
CREATE TABLE Words( id INTEGER PRIMARY KEY, word TEXT NOT NULL UNIQUE COLLATE NOCASE, meaning TEXT, class TEXT, synonyms TEXT );
Now I will imagine that there is a new table created (if I understand the OP)
CREATE TABLE Words2( id INTEGER, word TEXT NOT NULL UNIQUE COLLATE NOCASE );
Now I suppose many words have been duplicated into this Words2 table, but some rows have been deleted/omitted, and the objective is to now make this Words2 table have the same columns and data (for the included rows) than what the original "Words" table had.
ALTER TABLE Words2 ADD COLUMN meaning TEXT, Class TEXT, Synonyms TEXT ; UPDATE Words2 SET (id, meaning, Class, Synonyms) = ( SELECT Words.id, Words.meaning, Words.Class, Words.Synonyms FROM Words WHERE Words2.word = Words.word );
This adds the columns, updates them and updates the Key column. After this you will probably need to rename the table and still either switch off foreign keys or drop the foreign key constraint to replace the Words table - if indeed that was the plan.
PS: Make backups. I typed the above from my head, there's bound to be a mistake (or ten) in there somewhere, though any errors should be easy enough to fix.