1. You have a table customers with a column email that currently allows NULL values. Modify the table so that future entries must always have an email.
To our existing customers table, we alter the table customers and alter the column email to set it to not null so that the future entries always have a value for the email and they are no longer null.
2. In the users table, ensure that the username column is unique across all records using an ALTER statement.
To our existing users table, we alter the table and add a unique constraint to the username column so that no two rows can have the same username.
3. In the products table, enforce that price must always be greater than 0 using an ALTER command.
Here we alter the products table and add a check constraint on the price column to make sure all future values are greater than 0.
4. Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.
We alter the orders table and set a default value for the status column so that if no value is given, it automatically becomes 'pending'.
5. Alter the employees table by adding a new column salary such that It cannot be NULL. It must always be greater than 10,000.
We alter the employees table by adding a new column salary and apply not null along with a check constraint to ensure the value is always greater than 10000.
6. Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.
Since the foreign key already exists, we alter the employees table by dropping the existing constraint and adding it again with on delete cascade so related employees are removed when a department is deleted.
7. In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.
We alter the accounts table and drop the existing check constraint on balance so that this restriction is no longer enforced.
8. In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.
We alter the payments table and add a composite unique constraint on user_id and transaction_id so that the same pair cannot appear more than once.
Top comments (0)