Hey everybody, in this topic I'm going to explain the default constraint. When inserting a new row, if we do not specify a value for a column, by default we can add some value that we set. Here's an example. Let's select all from our products table. Select all from products.
In an earlier example, we have a table of products. A few items on the menu for sale are hamburgers, fries, soda, and ice cream. Let's add a few items on the menu that would be free.
This could include napkins, straws, forks, and spoons. Something you would commonly see at a fast-food restaurant. If we're not using the default constraint, we would have to enter in those prices manually.
So let's insert into our table products, our values, Product ID 104 will be a straw. The price will be $0.00. Let's do this all together. We have 105, 106, 107. Product number 105 will be a napkin. The napkin is $0.00.
106 is a plastic fork. Again $0.00. 107 is a plastic spoon.
The price is $0.00. Then I will select all from my products table. Here's our new products table. We have our four items that actually cost something, then our free items, which are included with whatever we buy.
In place of explicitly stating a price, one way in which we can make our lives easier, if we're adding a bunch of free items to the menu, we could set a default constraint where if we don't explicitly set a price, the price will default to be zero. It will be free. So I'm going to undo everything that we just did. delete from products where product id is greater than or equal to 104. And then we can execute that statement.
If we were recreating this table with the default constraint, we would type create table, the name of the table, products in this case, add our columns, we have a product id, The data type is integer. We have a product name. The data type is varchar, and I forgot what the size was.
It looks like 25. Then a price, which is a decimal. MaxDigitSize of 4, Precision of 2. Now let's use the default constraint. After the column, you would like to add that constraint too.
Type default, then some value. I will set the default value to be zero dollars and zero cents. Or you could just say zero too, that'd be fine.
You would just add this constraint when creating a new table. However, I do already have a products table, so this technically wouldn't work because that table already exists. Now to alter a table to include that constraint, you would instead type.
type alter table, the name of the table, alter the name of the column, price, set, default constraint, then the value, so zero. And that appears to have worked. Let's select our products table, select all from products, then we will insert some new rows.
insert into products than our values. So we had four rows, 104, that was a straw, 105 was a napkin, 106, that was a straw, was a fork, 107 is a spoon. Then finish the statement with a semicolon. So since we set that default constraint, we don't need to explicitly list the price. But we are missing one thing though.
When I execute this statement, the column count doesn't match. So when we insert into products, we will list what we're inserting explicitly, the product ID and the product name. After our table name, within parentheses, type product.
ID, then product name. Now this should work. Yeah, and here's our products table. With our four inserts, all of the prices were set to zero, and we did not need to explicitly state that this time. If you don't specify a value, you'll use whatever the default is.
Here's another example. I think you'll like this one. Let's say we have a table of transactions. After inserting a new transaction, we will insert a timestamp of when that transaction took place, and that timestamp will be the default. Create table transactions.
At the end of this topic we will delete this table, so don't get too attached to it. We will have three columns. TransactionId, which will be of the data type integer.
A transaction amount, let's just say amount. The data type will be decimal. Five digits, precision of two is good. Then a transaction date.
The data type will be date. Actually, let's make this date time. I'd like to include the time of when this transaction happened.
Now for the default constraint, after the date time, let's add default, then the now function. We don't need to explicitly add the date and time. That will be done automatically, which is kind of cool.
So I'm going to create this table. Let's select all from transactions. If we're going to insert some values we'll need at least a transaction ID and amount.
The transaction date will be included automatically. So let's insert into transactions some values. Let's do one at a time. The first transaction of the day will have a transaction ID of 1, I suppose.
A customer came in and bought a hamburger and a soda for a total of $4.99. Then we do need to list the transaction ID, then the amount. After our table name, add transaction ID, then amount. So let's run this. Yeah, cool.
So there's our transaction ID. This was order number one. The total amount was $4.99.
And we do have a timestamp of when this transaction occurred. So the second order of the day will be for a total of $2.89, and that happened at this time. Then the third order of the day will have a total price of $8.37. Yep, there we are.
So we don't need this table anymore. I'm going to drop table transactions. We will recreate this table in the future, but I'd rather start fresh. Alright everybody, that is the default constraint. When you create a table or alter a table, you can set a default value of some value you specify, then whenever you insert a row, that value will be included automatically, which is pretty useful.
But yeah, that is the default constraint in MySQL.