Make your database, and by extension your application, more performant.Start learning now
In this article, we’ll take a look at how to safely add and drop columns from a SQL database using
ALTER TABLE … ADD and
ALTER TABLE … DROP COLUMN.
We will also briefly touch on adding and dropping constraints on SQL tables, since that functions in the same way and since it’s important to consider constraints anyway when you’re adding columns to a table.
But first, a quick note…
While there are a variety of SQL “flavors” for relational databases, adding and dropping columns is generally handled the same way in each of them. We’re using CockroachDB syntax in this article, but the same commands will generally work in MySQL, SQlite, Oracle, and other SQL databases.
One thing to watch out for, though, is data types. While relational databases all tend to support most of the same types of data, they aren’t always called the same thing, and they don’t always function in precisely the same way. For example, in this article we’ll be adding a column using the
STRING datatype in CockroachDB, but in some other relational databases this would be called
VARCHAR, and in some cases you might have to define a length limit in bytes or characters. Refer to the documentation for your specific database software to ensure you’re using the correct data type for your use case.
Let’s start with an example table, so that we can walk through the process of adding a column, adding a constraint, dropping a constraint, and then dropping the column.
We’ll start with this very simple table, called
That doesn’t include much information, so let’s add a column to make it a little more useful.
If you’d like to follow along with this article in your own database, here’s a quick SQL statement to copy-paste that will create the above table (though you may need to adjust the data types depending on your RDBMS):
CREATE TABLE users ( id INT PRIMARY KEY, first_name STRING ); INSERT INTO users (id, first_name) VALUES (1, 'ellen'), (2, 'parker'), (3,'ash');
ALTER TABLE … ADD COLUMN
The basic syntax pattern for adding a column is:
ALTER TABLE table_name ADD COLUMN column_name datatype constraint
In the above command, we need to replace all of the lower-case elements with the specifics of the column we want to add. (Note that the word
COLUMN is optional in many RDBMS;
ALTER TABLE table_name ADD column_name will also work.)
users table is missing an
Here’s the “add column” command with all of the above constraints added:
ALTER TABLE users ADD COLUMN email STRING UNIQUE NOT NULL;
That will add a new column to our
users table that’s called
STRING, they must be unique in the column, and they cannot be null.
However, running this command with our existing table won’t work. That’s because we’re trying to add a column to the table without adding values to it, but our constraints say that values in this column can’t be null.
There are a variety of ways we can work around this, but we’ll take a simple step-by-step approach here so that we can also demonstrate how to add constraints.
To do this, we can simply run the above command without the constraints, like so:
ALTER TABLE users ADD COLUMN email STRING;
Running this command will result in the following table:
So far, so good!
Again, there are other ways to do this, but to keep things simple and clear we’ll use
UPDATE statements to update each row in sequence:
UPDATE users SET email = 'firstname.lastname@example.org' WHERE id = 1; UPDATE users SET email = 'email@example.com' WHERE id = 2; UPDATE users SET email = 'firstname.lastname@example.org' WHERE id = 3;
This results in the following table:
That looks perfect, but we haven’t added our
NOT NULL constraints yet. In CockroachDB we can quickly verify this using the
SHOW CONSTRAINTS command:
SHOW CONSTRAINTS FROM users;
table_name | constraint_name | constraint_type | details | validated -------------+-----------------+-----------------+----------------------+------------ users | users_pkey | PRIMARY KEY | PRIMARY KEY (id ASC) | t (1 row)
As we can see, the only constraint on this table right now is the primary key. Let’s add our constraints to the
Constraints are added to columns differently, depending on the specific constraint we’re adding. In this case, we’ll need to use two slightly different commands to add our
NOT NULL constraints. (Again, we’re using CockroachDB/PostgreSQL syntax here, your RDBMS may handle this slightly differently).
First, let’s add the
UNIQUE constraint, which we can do with
ALTER TABLE … ADD CONSTRAINT:
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
Note that in the above command, we’re also giving the constraint a descriptive name,
email_unique so that we can easily tell what it is when looking at a list of constraints.
Next, we’ll add the
NOT NULL constraint using
ALTER TABLE … ALTER COLUMN:
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
We’ve successfully added a column, some data, and constraints to that column to our SQL table.
We can try to insert some bad data to see that our constraints are working as intended. If we try to insert a fourth row with a
NULL value in the email column…
INSERT INTO users (id, first_name, email) VALUES (4, 'lambert', NULL);
… we can see that our
NOT NULL constraint prevents this row from being added to the table, as it should:
ERROR: null value in column "email" violates not-null constraint SQLSTATE: 23502
If we try to insert a fourth row that includes an email that already exists in the table…
INSERT INTO users2 (id, first_name, email) VALUES (4, 'lambert', 'email@example.com');
…again, we can see that the database is rejecting it, as expected due to the
UNIQUE constraint we added.
ERROR: duplicate key value violates unique constraint "email_unique" SQLSTATE: 23505
So, we’ve successfully added a column. Now, how do we do the opposite?
ALTER TABLE … DROP COLUMN
To drop a column from a table, we will use a very similar-looking command to the one we used to add a table. The basic syntax looks like this:
ALTER TABLE table_name DROP COLUMN column_name;
However, dropping a column means removing data, which is always dangerous. And although we’re only working with a single table here, in a real-world database a column may be indexed, and it might be referenced by other tables via foreign keys, so we need to approach dropping columns cautiously.
With dropping columns in particular, it’s important check the documentation for your specific RDBMS and version, as different databases handle it differently and have different security measures in place.
In CockroachDB if we try to drop a column, by default we’ll get an error:
ALTER TABLE users DROP COLUMN first_name;
ERROR: rejected (sql_safe_updates = true): ALTER TABLE DROP COLUMN will remove all data in that column.
This is a security feature, designed to ensure we don’t accidentally delete data we didn’t intend to. To remove the column, we’ll need to first turn
SET sql_safe_updates = false;
Now we can drop the column:
ALTER TABLE users DROP COLUMN first_name;
And we can see that this worked by viewing our table again:
If the column you intend to drop is part of one or more indexes, it can still be dropped using the same commands described above.
However, it’s important to be aware that dropping an indexed column will also drop all of the indexes that used that column.
We sometimes have columns in a table that are referenced in another table via foreign keys, or that are referenced by other SQL objects like views.
If we want to drop a column only if it has no dependent objects, in CockroachDB we can add
RESTRICT to specify that the column should only be dropped if it has no dependent objects:
ALTER TABLE users DROP COLUMN first_name RESTRICT;
Alternatively, we might want to drop a column and its dependent objects. To do this, we can add
CASCADE to ensure that dependent objects get dropped along with the column. Needless to say, this command can delete a lot of data, so it’s important to be sure you know what those dependencies are before you run it!
ALTER TABLE users DROP COLUMN first_name CASCADE;
Again, there is slightly different syntax we need to use depending on the constraint we’d like to drop. And again, there are some specifics that will vary based on your RDBMS.
In regular PostgreSQL, to drop the
UNIQUE constraint we added to the table, we can use
ALTER TABLE … DROP CONSTRAINT like so:
ALTER TABLE users DROP CONSTRAINT email_unique;
Most relational databases will handle dropping a
UNIQUE constraint similarly. In CockroachDB specifically though, dropping
UNIQUE constraints is handled differently. We can remove the constraint by removing the index that was automatically created when it was added, like so:
DROP INDEX email_unique;
What about our
NOT NULL constraint?
Just as we did when adding it, we’ll need to use
ALTER TABLE … ALTER COLUMN to drop the
NOT NULL constraint:
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
Again, we can confirm this worked by adding new data and checking the table. For example, if we add a fourth row to our table with a null email field after running the above command to drop the
NOT NULL constraint, we’ll get this:
(Recall that we removed the
first_name column in an earlier step; that is why it is missing here).
Feeling like you’ve mastered adding and dropping columns and constraints from SQL tables? A great next step is to make your relational database, well, relational by using foreign keys to add data relationships between tables. And don’t forget to watch out for common foreign key mistakes!
This post was originally published in 2018 by former CockroachDB engineer Matt Jibson, who owns goats and makes his own …Read more