Creating a table right the first time is ideal but in real projects tables change. Business rules shift, new requirements come in, and things that were optional before become mandatory. This set of problems is all about using ALTER TABLE to modify existing tables without throwing everything away and starting over.
1. Making Email Mandatory on an Existing Table
The customers table already exists and the email column currently allows NULL. To make it required going forward you add a NOT NULL constraint.
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
One thing worth knowing here is that if there are already rows in the table with NULL in the email column PostgreSQL will reject this command. You would need to fill in those empty emails first before the constraint can be applied. The database protects you from creating a constraint that existing data already violates.
2. Adding a UNIQUE Constraint to an Existing Column
The users table has a username column but it was created without a uniqueness rule. To enforce it now without recreating the table:
ALTER TABLE users
ADD CONSTRAINT users_username_unique UNIQUE (username);
Giving the constraint a name like users_username_unique is optional but it is a good habit. Named constraints are much easier to drop later if you ever need to remove them.
3. Adding a CHECK Constraint to Price
The products table needs to enforce that price is always greater than zero. CHECK constraints can be added to existing tables the same way.
ALTER TABLE products
ADD CONSTRAINT products_price_check CHECK (price > 0);
Again PostgreSQL will check existing rows before applying this. If any product already has a price of zero or below the command will fail until those rows are fixed.
4. Setting a Default Value on an Existing Column
The orders table has a status column but no default was set when it was created. Adding a default now means any future inserts that do not mention status will automatically get pending.
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
This only affects future inserts. Existing rows that already have NULL in status are not touched by this command.
5. Adding a New Column with Constraints
This one adds a brand new salary column to the employees table. The column cannot be NULL and must always be above 10000.
ALTER TABLE employees
ADD COLUMN salary NUMERIC NOT NULL DEFAULT 10001
CHECK (salary > 10000);
The DEFAULT 10001 is needed here because if there are existing rows in the table PostgreSQL needs to know what value to put in the new salary column for those rows. Without a default it cannot add a NOT NULL column to a table that already has data. Once the column is added and the existing rows have values the default can be removed if you want to force future inserts to always provide a salary explicitly.
6. Changing a Foreign Key to Use CASCADE on Delete
Foreign key constraints cannot be modified directly. To change the behavior you drop the old constraint and add a new one with the CASCADE rule.
First find and drop the existing foreign key:
ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;
Then add it back with ON DELETE CASCADE:
ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;
Now when a department is deleted all employees linked to that department are automatically removed as well. The two step drop and recreate is just how PostgreSQL handles constraint changes.
7. Removing a CHECK Constraint
To remove a constraint you need its name. If you named it when you created it this is straightforward. If you did not name it PostgreSQL generated one automatically and you can find it by checking the table definition.
-- Check the constraint name first
\d accounts
-- Then drop it
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;
Once dropped there is nothing stopping a balance from going negative. Make sure removing a constraint is actually what you want before running this because there is no undo in a live database without a backup.
8. Adding a Composite Unique Constraint
The payments table needs to make sure that the combination of user_id and transaction_id is unique. The same user cannot have the same transaction ID twice.
ALTER TABLE payments
ADD CONSTRAINT payments_user_transaction_unique
UNIQUE (user_id, transaction_id);
This works the same as defining a composite unique constraint at table creation time. The combination of both columns has to be unique. Each column on its own can repeat, but the pair together cannot.
What This Set Taught Me
ALTER TABLE is something you will use constantly in real projects. Schema design is never finished on day one. Requirements change, bugs get caught, and data quality rules get added as the system grows.
The key habit to build is naming your constraints. It costs nothing when you create them and saves a lot of pain when you need to find and drop them later. The other thing worth remembering is that adding constraints to tables with existing data is not always instant. PostgreSQL validates every existing row before it accepts the new rule which is the right behavior even if it feels slow on large tables.
Top comments (0)