[Solved] Update table design in SQL

[basejumper]

Hello!

Apologies if this is a really basic question, but I would appreciate a bit of clarification as to how I update a table design using SQL in Base. I have managed to design two connected tables using SQL (Tools...SQL), and all seems OK, not bad for a girl's first attempt at SQL! My question is, how do I proceed if I want to alter the design of either of the tables, or if I want to add another connected table, using SQL? I can't find a way of retrieving my previous SQL command, and I didn't save it/print it out. Do I have to start again from scratch? Thank you in advance.

[MTP]

You can alter your table however you want through the Tools->SQL window. Just knowing the table name and column names (which you can see through the GUI) is enough to compose your SQL statement, you don't need to have the original CREATE statement on hand. The exact syntax depends on what change you are trying to make (retype a column, add a new column, change the primary key, add a foreign key relationship, copy data from one column to another, etc.)

Most table setup and modification can be done with the GUI, too, and IMO that method is more intuitive.

[basejumper]

Thanks for your prompt response MTP, and for taking the time to answer my query. I thought I'd give SQL a go, as I had read many times that this is the *proper* way to create a database, (and I like a challenge!), but I'll have a go at using the GUI too. I'm a bit worried now though because the original statement included a DROP command, if I did make modifications using SQL, will this erase what I've already done?

Thanks once again, your help is much appreciated

[FJCC]

If you are just modifying a table, you will not include a DROP TABLE statement. If you can give an example of the kind of modification you have in mind, we could give an example of the appropriate SQL command.

[basejumper]

Hi FJCC. Thanks for your reply, and for your kind offer of help. With regards to the aforementioned DROP TABLE statement, it was in my original command, and I wondered whether any further SQL commands might force the DROP TABLE statement, as I think that I read that that was the purpose of the statement, to effectively over-ride any previous statements, but I realise now, from your reply, that it is only if I include the DROP statement in the new command, will it be utilised. Makes perfect sense, when you think about. (I am new to this, can you tell?)

With regards to any changes that I wish to make, I should have explained more fully. I am just starting out on creating what may become a rather large database, to help with the running of a business. In order to find my feet with SQL, I started with what I thought would be the easiest component of the database, and will build on this as and when my brain can cope! So over time, I will be adding many (many!) more tables, and may have to edit the existing tables, as I become more fluent with SQL and building databases. Forewarned is forearmed, as they say, and I wondered whether there was a way to specifically edit a previous SQL command, but based on what you and MTP have kindly taken the time to explain, I can simply add new code where required. Hurrah! I think that it may be prudent to write the SQL in notepad (or similar) first, and that way I can keep track of everything.

Thanks once again, problem solved, I think

[MTP]

It sounds like you're off to an excellent start. In case you haven't come across it, I'd like to suggest reading about "normalization" - it is a way of setting up tables that makes your higher-level actions easier later on.

Also, saving your past SQL commands is a good idea, and notepad definitely works. If you want to save manipulation SQL inside the Base file, you can paste it into a query and turn on Edit->Run SQL Command Directly. The SQL won't run from there (the query window in Base will only run SELECT statements), but you can save it with formatting for reference.

[basejumper]

Fantastic tips, thank you MTP. I'm thinking now about investing in an SQL book, so that I have a more comprehensive guide to hand. It certainly makes a change from the programming I used to do in BASIC, many moons ago!