On Monday, May 14, 2012 7:58:57 PM UTC+7, Ross Peoples wrote:
>
> Some databases allow you to temporarily turn off auto-generating IDs so
> that you can import a table keeping its original IDs intact. I wrote a
> database abstraction layer a few years back for another project that
> specifically allowed you to make an identical copy of a database from a
> MySQL database to a PostgreSQL database on a different machine and
> preserving the IDs was something I needed to figure out. Here is how you do
> it with MSSQL, MySQL, and PostgreSQL. Maybe Massimo can add this as an
> option to the DAL's import method:
>
> First, import your records, then run these on your database engine:
>
> MSSQL:
> db.executesql('SET IDENTITY_INSERT dbo.mytable OFF');
> db.executesql('DBCC CHECKIDENT (mytable)');
>
> MySQL:
> count = db.executesql('SELECT MAX(id) FROM mytable;')
> count += 1
> db.executesql('ALTER TABLE mytable AUTO_INCREMENT=%s;' % count)
>
> PostgreSQL:
> count = db.executesql('SELECT MAX(id) FROM mytable;')
> count += 1
> db.executesql('ALTER SEQUENCE mytable_sequence RESTART WITH %s;' % count)
>
> For all 3 database engines, this sets the auto-increment counter to the
> max ID it finds in the table (AFTER the import) +1 so that new rows will
> have the proper IDs.
>
Do I understand correctly that without this addition it is impossible to
maintain referential integrity of a database when exporting its tables as
csv files and then importing them back?
