[MySQL] Day 8. DEFAULT Constraint

Today I learned about DEFAULT constraint in MySQL!!!

 

W3Schools.com

 

As you can guess from its name, 

DEFAULT constraint is used to set a default value for a colum.

 

Let's start by checking our previously made table, products.

SELECT * FROM products;

 

I guess we added enough foods, so let's add some free utensils and napkins.

We don't know how to use DEFAULT constraint yet, so we gotta add all them manually. 

-- Manually adding items
INSERT INTO products
VALUES  (5, "Straw", 0.00),
        (6, "Napkin", 0.00),
        (7, "Fork", 0.00),
        (8, "Spoon", 0.00);

 

It's tedious to manually type 0.00 for all new products. 

 

Instead, we can set DEFAULT constraint for default values for prices, set to $0.00. 

If we are making a new table with DEFAULT constarint,

CREATE TABLE products(
    product_id INT,
    product_name VARCHAR(25),
    price DECIMAL(4,2) DEFAULT 0.00
);

 

But, we have our products table already, so we only have to add the constraint to price column. 

ALTER TABLE products
ALTER price SET DEFAULT 0.00;

 

Now the DDL in Table Inspector shows that price table got its DEFAULT value as 0.00. 

 

Let's make use of this DEFAULT constraint. 

INSERT INTO products (product_id, product_name)
VALUES  (5, "Straw"),
        (6, "Napkin"),
        (7, "Fork"),
        (8, "Spoon");
        
SELECT * FROM products;

Don't forget to specify the columns that we are inserting! (product_id, product_name) 

 

You can see that even though I didn't explicitly specify the price of new items, the price value are set to 0.00 by default. 

 

 

Here's another useful example of DEFAULT constraint. 

Say we're constructing a database for bank transaction.

Each transaction will be recorded in the database with the time of transaction occurred.

Remember NOW() function for generating the current DATETIME? (Check previous post)

CREATE TABLE transactions(
    transaction_id INT,
    amount DECIMAL(5, 2),
    transaction_date DATETIME DEFAULT NOW()
);

 

Now let's try inserting couple of transactions. 

INSERT INTO transactions(transaction_id, amount)
VALUES (1, 4.99);

SELECT * FROM transactions;

 

Adding another shows:

INSERT INTO transactions(transaction_id, amount)
VALUES (2, 49.99);

SELECT * FROM transactions;

 

 

If you want to change the default value for DEFAULT constraint, you can do the exact same thing that you did to add a DEFAULT constraint above.

 

Lastly, if you want to drop the DEFAULT constraint, 

ALTER TABLE products
ALTER prices DROP DEFAULT;