Thread: Re: Access-postgreSQL migration (extra invalid foreign key added)
> -----Original Message----- > From: Reshat Sabiq [mailto:sabiq@purdue.edu] > Sent: 22 December 2002 14:00 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] Access-postgreSQL migration (extra > invalid foreign key added) > > > Hi, > > I have an Access DB, that's been running OK in Access, and as > part of a web app based on JDBC. When I convert it to > postreSQL, all table creation, data insertion, and primary > key creation statements complete just fine. > However, the first foreign key attempt fails: > > Creating Foreign Key: CompanyContactInfo > SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT > "CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES "Company" > (id) ON DELETE CASCADE ON UPDATE CASCADE SQL (DBName): ALTER > TABLE "ContactInfo" ADD CONSTRAINT "CompanyContactInfo_fk" > FOREIGN KEY(id) REFERENCES () ON DELETE NO ACTION ON UPDATE > NO ACTION SQL (DBName): ROLLBACK > > I'm pretty sure that Access doesn't have a foreign key to > null table and null column and would appreciate any > suggestions as to why this 2nd statement might be coming up. > Might there be a bug either somewhere? It's certainly possible. The Migration Wizard is a bit hit 'n' miss to be honest as I wrote it without ever migrating a database except for the odd test using Northwind/Biblio. Is it possible to get a schema only copy of your mdb file to look at please? > P.P.P.P.S. I once looked up SQL statements corresponding to > tables or queries in an older version of Access, but I can't > find a way to do the same in the current one (not for table > definitions anyway). That might be of a little help. Although > the best I can think if is that \r\n is processed incorrectly > by ODBC bridge or pgAdmin, and that results in null table > with null column. We do sort of assume there are no \n's or \r's in your object names... Regards, Dave.
-----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Sunday, December 22, 2002 2:31 PM To: Reshat Sabiq; pgadmin-support@postgresql.org Subject: RE: [pgadmin-support] Access-postgreSQL migration (extra invalid foreign key added) ------- It's certainly possible. The Migration Wizard is a bit hit 'n' miss to be honest as I wrote it without ever migrating a database except for the odd test using Northwind/Biblio. Is it possible to get a schema only copy of your mdb file to look at please? ------- Sure. How should I get that schema? I have the postgreSQL statements in pgAdmin, but I don't know how to obtain the same from Access. --------- We do sort of assume there are no \n's or \r's in your object names... --------- That is correct; I do not have new lines in object names. In fact, I shouldn't have any in that database. But I was thinking it might be there behind the scenes. But it is strange that only 1 out of 10 foreign keys get the invalid duplicate statement. P.S. Thanks for the great tool. Things like that can get done with time. Sincerely, r.
> -----Original Message----- > From: Reshat Sabiq [mailto:sabiq@purdue.edu] > Sent: 23 December 2002 02:58 > To: Dave Page; pgadmin-support@postgresql.org > Subject: RE: [pgadmin-support] Access-postgreSQL migration > (extra invalid foreign key added) > > > > > -----Original Message----- > From: Dave Page [mailto:dpage@vale-housing.co.uk] > Sent: Sunday, December 22, 2002 2:31 PM > To: Reshat Sabiq; pgadmin-support@postgresql.org > Subject: RE: [pgadmin-support] Access-postgreSQL migration > (extra invalid foreign key added) > > > ------- > It's certainly possible. The Migration Wizard is a bit hit > 'n' miss to be honest as I wrote it without ever migrating a > database except for the odd test using Northwind/Biblio. > > Is it possible to get a schema only copy of your mdb file to > look at please? > ------- > Sure. How should I get that schema? I have the postgreSQL > statements in pgAdmin, but I don't know how to obtain the > same from Access. Sorry, I don't know about Access. The statements from pgAdmin are of little use though as I want to see what it should have generated, not what it did. > --------- > We do sort of assume there are no \n's or \r's in your object names... > --------- > That is correct; I do not have new lines in object names. In > fact, I shouldn't have any in that database. But I was > thinking it might be there behind the scenes. But it is > strange that only 1 out of 10 foreign keys get the invalid > duplicate statement. > > P.S. Thanks for the great tool. Things like that can get done > with time. You're welcome. Regards, Dave.
My integer types migrated from Access one level lower than is shown in the Map in the wizard. Thus, integer became int2 instead int4 Also, long integer became int4 instead int8, although long integer wasn't in the map so that probably doesn't count formally. At the same time, byte became int2, but apparently that is the only possible way because I'm not seeing an 8-bit integer type in postgres (please let me know if there is one). P.S. I'd like to suggest enhancements for pgAdmin3: 1) getting integer to map into int4 2) adding a source type 'long integer' with a mapping to int8. In the long run, it would also be nice to have an 8-bit integer type if there isn't one yet. Sincerely, r.