Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
As you're defining your table you may want to define specific rules and behaviors for some of your columns. This is done by using constraints. I'm going to use the Scratch database for this lesson. And I'm going to copy and paste some code here out of the Chapter 3 exercise file, here around line 73. Everything from drop table to select from test. Press Cmd+C to copy, and Cmd+V to paste. That's Ctrl+C and Ctrl+V, if you're using a PC.
And you see, we start here with a drop table. Because, again, we're going to be creating this table over and over again with different options. So we need a drop table at the start of that. And, then we have the create table, which is two columns, an integer, and a varchar. And, we'll insert a row into it, describe it and select from it. And so that looks like this. There's our table and there's our one row. In this example you'll notice that the A column has a null value because no data was added to that column.
If you look here at our insert. We're only inserting data into row B. And so, when no default value is specified, MySQL uses null as a default. If you want to disallow null values, you can add a not null constraint to the column. So that would look like this. So here in our column definition we have the type, and the type is followed by various constraints and in this case we have one constraint which is not null. And you notice in the describe here, it says under null, it says yes.
It'll say no when we redefine the table. And because there's no default value specified, the default value is null, it gives it a zero, because that's the closest that the database engine can do. Other database systems handle that situation differently, but this is what MySQL does with that. When you have not null for an integer, and you do not define a default value. You get a zero. Now you can define a default value right out here in the constraint, so you can say default and give it a value say 47.
And now when I press Go you notice the default here in the describe is set at 47 and our insert which does not specify a value for a. Puts 47 in that row, if I try to specially insert a null. So, I'll say a and b in my insert here and I'll try and put in a null. This will give me a constraint violation error, integrity constraint violation, column a cannot be null. And the insert failed.
So there is no row for the select statement. So we'll go ahead and put that insert back without the value there. And I'm going to show you another constraint. This constraint is called the unique constraint. And if I say unique here, for the b column. And press Go, you'll notice that I now have a key, and it says, unique. And in fact, if I come in here and I say, Show Index From, you'll notice that it's created an index so that it can enforce that uniqueness.
And if I come in here and I try to. Insert the same row a few more times, I'll get an integrity constraint violation, duplicate entry one for key b, because this index is set up as unique. You see non-unique is zero and key is set to unique here and that's because of this unique constraint there in the column definition. Now keep in mind that MYSQL treats null as a special case. It is not a value, and so adding multiple nulls to this column does not count as a integrity constraint violation.
You see we have several nulls there because null is not a value. Null is the absence of value. So if you want to actually prevent that from happening, you can use not null as an additional constraint. And now it says column b cannot be null. And we don't have those nulls at all. So, constraints create rules governing the behaviors of columns in your tables. MySQL supports several different kinds of constraints and we'll explore more of them later in this chapter. For now I'm going to go ahead and drop this table to return our database to its original state for the rest of the course.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 96908 Viewers
56 Video lessons · 110593 Viewers
71 Video lessons · 79422 Viewers
131 Video lessons · 38108 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.