Join Bill Weinman for an in-depth discussion in this video Controlling column behaviors with constraints, part of SQL Essential Training (2014).
As you're defining your schema, you might want to define specific rules and behaviors for some of your columns. This is done using constraints. For this lesson, we're using the in-memory database. And, I'm going to start by creating a table, so I'll type create table and call it test. And we'll give it three columns, each of them text columns. And we'll insert a row of data into it. So here I'm just inserting data into the a and b columns. The c column, I'm not inserting any data into it so we would expect it to be a null.
And we'll go ahead and take a look at the row. And I'll press the go button. And there's our result, we have one and two in columns a and b and a null in column c. It's exactly what we expect. So column c has this null because we didn't specify a value for column c. I can use a constraint called not null, in the definition of the column. And then this behavior will no longer be allowed. And so, this insert will generate an error. If I press go here, you see, we have an integrity constraint violation, test.c, that's the c column in the table test, may not be null, because we have this constraint here in the definition of the column, that says it cannot be null.
So instead of a not null constraint, you may want to give the column a default value, like this. And so now, when you don't specify a value for that column, the word panda will be inserted instead as the default. So when I press go, colum c now has the value panda. So even though I did not specify a value it gets that value because that is the default constraint. And other common constraint ensures that every value in a column is unique. So here in the a column I can put in the unique constraint.
Like that. And then let's just duplicate this insert with exactly the same values. And this should generate an error. So when I press go we get an error in query three. That's the third one down here the insert. And another integrity constraint violation. Column a is not unique. So if we change that value in column a and press go. Now you see that it works. And we have the other column that says seven.
Now it's worth noting that, depending on your database system, null values may be exempted from the unique constraint. So if instead of this one here, I had a null, and I have another null Now when I press go, you'll notice that it allows those two nulls even though we have the unique constraint. This is a really common behavior. Most of the major database systems actually operate this way. If you don't want this behavior, there may be a configuration option in your system to change it or you can combine two constraints and use the not null constraint along with the unique constraint like this.
And now null values are forbidden in that column altogether. When I press go, you see I get integrity constraint violations for both of those. So these are some of the most common constraints. Most database systems also have additional constraints that work in their particular environment. You'll need to consult the documentation for your database system to learn what additional constraints may be available.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.