Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Changing the PRIMARY KEY ... |
Thu, Aug 15 2013 1:01 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | I have successfully migrated my database from DBISAM, but decided to
complicate matters by doing so long delayed table and column name changes. This is what I did amongst other things:- -------------------------------------- SCRIPT () BEGIN ... EXECUTE IMMEDIATE 'RENAME TABLE Landlords TO Owners'; EXECUTE IMMEDIATE 'ALTER TABLE Owners RENAME LandlordCode TO OwnerCode'; ... END -------------------------------------- Which is OK as far as it goes. BUT LandlordCode was the Primary Key of the Landlords Table (now Owners Table) and when I look at the Primary Key of the Owners Table with EDBMgr, I see that the (now non-existent) LandlordCode column is still the primary Key. Gosh, what do I do now? My first thought was to change the primary key using my script but I can't get to grips with the syntax ... ALTER TABLE Owners ALTER CONSTRAINT ..... something ... I didn't have a "next thought" Can anyone point me at an example? - can't find anything in the manual nor by searching the newsgroups. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Thu, Aug 15 2013 4:13 AM | Permanent Link |
Peter Evans | On 15/08/2013 3:01 PM, Jeff Cook wrote:
> -------------------------------------- > SCRIPT () > BEGIN > ... > EXECUTE IMMEDIATE 'RENAME TABLE Landlords TO Owners'; > EXECUTE IMMEDIATE 'ALTER TABLE Owners RENAME LandlordCode TO OwnerCode'; > ... > END > -------------------------------------- Something like :- EXECUTE IMMEDIATE 'RENAME TABLE Landlords TO Owners'; EXECUTE IMMEDIATE 'ALTER TABLE OWNERS DROP CONSTRAINT LandlordCode ...' EXECUTE IMMEDIATE 'ALTER TABLE Owners RENAME LandlordCode TO OwnerCode'; EXECUTE IMMEDIATE 'ALTER TABLE OWNERS ADD CONSTRAINT OwnerCode ... ...' You could spend a whole lotta time coding these changes. However there is an easier way. Please see my post "How to generate a script for altering objects in a database?" This talks about the COMPARE DATABASE statement. Unfortunately I do not know how to get the script and then run it. When I do I will be able to solve my own migration problems. Regards, Peter Evans |
Thu, Aug 15 2013 4:37 AM | Permanent Link |
Peter Evans | On 15/08/2013 6:13 PM, Peter Evans wrote:
> > However there is an easier way. Please see my post "How to generate a > script for altering objects in a database?" OK, here is a partial solution, I hope. I say hope as I won't be able to test this until tomorrow. Here is the broad outline :- Step 1. Perform your migration from DBISAM 3 remembering to migrate the data. Step 2. Within ElevateDB Manager choose to generate a script using Create, then on next tab sheet remembering to check Include Rows. Step 3. Save the script. Step 4. Create another database in a separate folder. Call the database <MigratedDatabaseName>Altered. Step 5. Switch to <MigratedDatabaseName>Altered Step 6. Run Reverse Engineer Database. Set Type = Upgrade. Step 7. Backup the database <MigratedDatabaseName> Step 8. Run the script against <MigratedDatabaseName>. Step 9. Run reverse engineer. Step 10. Using a text file difference editor to compare the two scripts. They should be the same. Note - I may have got source and target databases confused. I am not running the ElevateDB Manager on this machine... Unfortunately in Step 10 they won't be the same! Why, because there will be a difference in the date/time. Here is another suggestion - an option to suppress the date. Or the like. So that when automatic methods are used to compare scripts a false positive (or false negative?) won't arise. Regards, Peter Evans |
Thu, Aug 15 2013 6:54 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Peter
"Peter Evans" <pgevans@melbpc.org.au> wrote in message news:95A38765-9FD2-4916-96CA-0B9466B71DDB@news.elevatesoft.com... > > EXECUTE IMMEDIATE 'ALTER TABLE OWNERS > DROP CONSTRAINT LandlordCode ...' > Unless I've misunderstood something, I can't drop the constraint - I (perhaps mistakenly) believe that you can't have table without a PRIMARY KEY. If I can't drop it I can't recreate it. > You could spend a whole lotta time coding these changes. Not worried about lots of coding as the migration has been otherwise successful and although I have 50+ databases to migrate, they all have the same schema. It's late here now so I'll have to have another look in the morning - I'll play around with dropping the primary key ... Thanks Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Thu, Aug 15 2013 7:04 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | "Peter Evans" <pgevans@melbpc.org.au> wrote in message
news:652A6C6E-071A-4AE5-85E1-8A000167BF4D@news.elevatesoft.com... > On 15/08/2013 6:13 PM, Peter Evans wrote: > >> >> However there is an easier way. Please see my post "How to generate a >> script for altering objects in a database?" > > OK, here is a partial solution, I hope. I say hope as I won't be able to > test this until tomorrow. > > Here is the broad outline :- > Thanks for the effort you've put into answering my post - appreciated but .... I'm just writing a "quick and dirty" so the customer support guy can migrate 50 odd databases without getting too technical. I have a simple UI, (Select the DBISAM data folder, enter a databasename, press [Start] button). All the databases have the same schema and apart from fixing this table with the dodgy primary key all is ready to go ... .... except for the minor matter of fixing the code in 100+ modules, but that is another story Hopefully others can gain from your experience. Bright and early tomorrow I'll take another look at the d*mn thing Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Thu, Aug 15 2013 8:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
Quick answer: example ALTER TABLE "Adam" ALTER CONSTRAINT "PK" AS PRIMARY KEY ("Test2","Test4","Test3","Society") Teaching to fish answer: What I do (and just did) is to carry out the activity, whatever it is, in EDBManager and look in Explorer | SQL History This will tell you what you need Roy Lambert [Team Elevate] |
Thu, Aug 15 2013 4:38 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Peter and Roy
After pondering overnight I went back to my script and spotted an error - I had statement that just said:- EXECUTE IMMEDIATE 'ALTER TABLE Owners '; ... this was straight after the renaming of the primary key field. Take out that line and the renaming worked and the primary key changed too. Very weird ... wish I could deleted this thread and take an early weekend Thanks to you both for your assistance. Cheers Jeff |
Thu, Aug 15 2013 4:41 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Roy Lambert wrote:
> What I do (and just did) is to carry out the activity, whatever it is, in EDBManager and look in Explorer | SQL History What a great tip, Roy! I didn't know about the SQL History - that is going to be so useful in the future. Cheers Jeff |
Mon, Aug 19 2013 2:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jeff,
<< BUT LandlordCode was the Primary Key of the Landlords Table (now Owners Table) and when I look at the Primary Key of the Owners Table with EDBMgr, I see that the (now non-existent) LandlordCode column is still the primary Key. >> When you manually execute SQL via the SQL/Script windows, any DDL changes will not automatically be reflected in the current SQL metadata windows or the treeview. Just hit F5 to refresh them both. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |