SQL: Constraints

Instructor: Kaitlin Oglesby

Kaitlin has a BA in political science and experience teaching.

Do you want to make sure that the data entered into your SQL database meets certain criteria? This lesson on constraints will help make sure that each record has the required minimums to be recorded.

Constraining Data

Chances are that you're not playing with SQL for fun. Instead, you really want the work that you put into your database to shine through and be exactly what you want in the end. However, we all make mistakes sometimes. Wouldn't it be nice if there were a way to have SQL check your work as you were adding it, at least making sure that the information that you enter into a field is relevant? Better yet, since SQL is designed to be used by a large number of different people, wouldn't it be nice if there was a way to put a simple constraint on the type of data that can be entered in each field?

Luckily, just such a mechanism exists. Through using constraints, you can make sure that data entered in a field meets a minimum requirement to go there. From making sure that there is a value to making sure that it meets certain criteria, constraints help make sure your data is as accurate as possible.

Where Does the Constraint Go?

When creating a table, you put the constraint in right after the column and data type. Often, you'll see them set apart in brackets, but because SQL is so neutral about empty space and case, you probably won't be surprised to hear that those aren't necessary. However, to make things easier, let's set my constraints out in brackets, and encourage you to do the same - it just makes debugging much easier.

As mentioned, you list the constraint after the data type. For example, if we have the data column 'name' and want to make sure that an entry is put in for each name that has a length of up to 15 characters, you'd type it into SQL like this:

name VARCHAR(15) [NOT NULL],

Types of Constraints

Let's look at a few more constraints that you can use. Let's say that you were putting in employee ID numbers. Obviously, you want to be sure that those employee ID numbers are unique. Otherwise, you may confuse someone's paychecks! We'll use a column called 'id' and a data type of CHAR(6), since each ID number should be exactly six digits long. Finally, we'll add the constraint UNIQUE at the end, making sure to put it in brackets. Here it is in action:

id CHAR(6)[UNIQUE],```

Make sure that you are including the comma unless it is the very last column.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account