SQL allows the structure of a table to be altered in any way a developer needs. For all these queries we use the ALTER statement.
Alter SQL can basically be used for 3 tasks:
- Add columns.
- Modify the definition of the columns: restrictions and type.
- Remove columns from a table.
- Bases to create columns of a table in SQL language
- Add columns to an existing table
Bases to create columns of a table in SQL language
Create columns in a table is a task that always goes hand in hand with creating tables, since to create a column we always need a table that serves as a destination.
We can create columns in a table in two different ways:
- Defining them while creating a new table.
- Adding them to an already created table.
In this case I will address the second point.
Add columns to an existing table
Don’t panic if you created a table and forgot to add one or more columns! They can be added later without any kind of restriction and limit.
For add a column to an existing tableeven if it already had information, you have to use the ALTER command with the name of the table to be modified with the ADD statement followed by the COLUMN option, followed by the name of the new column, its type, and any other configuration of the column such as its default value, restrictions, or miscellaneous checks.
Now I’m going to add to the table videogame a “coding system” column to store in each record, i.e. each game, whether the game is PAL or NTSC:
ALTER TABLE videojuego ADD COLUMN codificacion VARCHAR(4);
As a point to tell you that I have indicated a maximum of 4 characters for this column, since I know that its maximum length will be 4 due to the word NTSC.
Finally, keep in mind that if the table already had data, you should specify a default value for it, otherwise an error would occur (the system would not know what data to fill in the values of that new column). For example, I’ll specify a default value of ‘PAL’ for the “encoding” column for all games:
ALTER COLUMN videojuego ADD COLUMN codificacion VARCHAR(4) DEFAULT ‘PAL’;
There are other configuration values and restrictions but I will leave them for another more complete and more advanced article.