Changing the structure of a table

6.1 Changing the structure of the database

And finally, the most delicious part of working with databases is changing the database. If you think that this is something very simple, then here is an anecdote for you on the subject:

The service station master, wiping his hands, hands over the car to the client and chats along the way:
- But who do you work for?
- I am a cardiac surgeon, I perform heart operations.
- And they pay a lot?
- $20,000 per operation.
- Nothing for myself ... But we, in fact, are doing the same thing, sorting out the engines, and they pay me only $ 300 ...
- Do you want to earn as much as I do?
- Want...
The surgeon starts the engine in the car and turns to the master:
- Move over!

The situation is exactly the same with databases. You want to take out a part of the data in the separate table? Okay, you need:

• create a new table
• copy the data into this new table
• remove columns from old table
• change all SQL queries that referred to the old table
• add SQL queries that will access the new table
• change the Java code that worked with tables in the old way
• change the backup and restore scripts, since now the table structures do not match
• if you have test data for developers, you need to change them too

Although in reality it is even worse:

New database structure:

• first you change the table structure on the developer's machine
• then you are convinced that such a structure is really better
• approve the new database structure

Migration scripts:

• you write scripts that will change table data, etc. on a real database
• run these scripts on a machine with the old database structure and see how it changes
• verify all the critical places of the new base

Update on sale

• stop the production database
• make a full backup
• executing scripts
• since there are usually a lot on the market, these scripts can work for hours

You launch the prod and hope that you don't have to roll everything back.

6.2 ALTER TABLE statement

On the other hand, the script itself, which changes the table structure, is very simple. In some ways, it is similar to a table creation script. General view of a table change request:

ALTER TABLE table
team 1,
team 2,
Team N

Commands are very different, but three main groups can be distinguished:

• ADD- adds something to the table
• MODIFY- changes something in the table
• DROP- deletes something in the table

And when I say something, it's not about the data, but about the structure of the table.

For example, you decide to add a new column to the table, then you need to execute the following query:

ALTER TABLE table
	ADD COLUMN Name type

Let's write a query that will add an email column to our employee table :

ALTER TABLE employee
	ADD COLUMN email  VARCHAR(10)

Now let's change the length of the email string from 10 to 100: for this we need a new script:

ALTER TABLE employee
	MODIFY COLUMN email VARCHAR(100)

If you want to change some attributes of a column, but not its type, then you need the command ALTER COLUM . Let's set the default email value:

ALTER TABLE employee
	ALTER COLUMN email VARCHAR(100)  DEFAULT 'test@test.com'

And finally, the column can be removed if you don't need it:

ALTER TABLE employee
	DROP COLUMN email

6.3 Adding and removing a primary key

And some more useful examples.

Let's say we forgot to declare the id column as the primary key. You can always do it separately via ALTER TABLE:

ALTER TABLE employee
	ADD PRIMARY KEY (id);

Removing it is even easier:

ALTER TABLE employee
	DELETE PRIMARY KEY;