Copyright

SQL DROP Constraint: Tutorial & Overview

Instructor: Sudha Aravindan

Sudha has a Doctor of Education Degree and is currently working as a Information Technology Specialist.

In this lesson, we will learn about the SQL drop constraint command, which is used to remove the constraint that limits the kind of data that can be saved in a database table.

What Is a Constraint?

A SQL or structured query language statement is a programming language used to communicate, create, delete, query, and make modifications to a database. In a database, a constraint is a rule for describing and limiting the kind of data that can be saved in a table or a column.

Some examples of constraints are:

  • The Sales-Per-Item column has to be of data type currency with two decimal place
  • The Last-Name column cannot be empty or null

Drop constraint is a method by which databases that use the SQL statement execute a command to remove a constraint that has been defined on the table or on one of the fields in the table. The constraint is like a rule; for example you may have had to create a password on the internet for access to your bank account, and you may get a message to the effect:

The password field has be to at least 20 characters in length.

This is an example of a rule or constraint that has been configured to ensure that you enter a strong password. Suppose many of the customers complain about the too long password requirements, the database programmer can then remove this constraint or rule so that another can be added later. This process of removing a rule that limits the kind of data that can be entered to the database is known as dropping a constraint.

Effects of Adding a Constraint

Imagine a grocery store cashier who is quickly keying in data for items that are not being scanned for the price. The constraint is the Sales-Per-Item column has to be of data type currency with two decimal places is in place. If the cashier enters 24 instead of $24.00, the program will generate an error. In this case, the constraint is helpful to make sure that only data in the correct format is stored in the database.

Dropping a Constraint

Any constraint that was created on a table or a column can be dropped or removed. Let's look at some examples of dropping constraints.

Example 1: Not Null Constraint

Consider a database table Customer-Table that has a field Customer-ID that requires the field to be not null. In this case, when the grocery store clerk enters a Customer's purchase without inputting a value for Customer ID, the program will generate an error message, and the data entered will not be saved. If we do not wish the Customer-ID field to be a required field, we can choose to drop this constraint or limitation, so that there will not be a requirement that there should be a value entered all the time for the ID field.

An SQL statement to drop the not null constraint from the Customer-ID table could look something like this:


ALTER TABLE Customer-Table alter column Customer-ID drop not null;


Example 2: Default Value Constraint

Consider a database table Customer-Table that has a field Order-Date that is filled in by default with today's date. When a customer exchanges a defective light bulb the cashier would like to keep the Order-Date as the date of the original purchase and not the date the item was exchanged. With the default constraint of the Order-Data that is filled in always with the current date, this is not possible. So the database programmer is asked to remove the default constraint.

The programmer would then write an SQL similar to this:


ALTER TABLE Customer-Table DROP constraint Order-Date-Constraint;


Here the Order-Date-Constraint is the name of the constraint, and the programmer is creating an SQL statement to drop the constraint by referring to it by its name.

Example 3: Primary Column Constraint

Sometimes in a database table one of the fields is identified as a primary key. A primary key is a unique value that identifies the data in the table. For example, in a database storing hospital records, Patient-ID could be the primary key because patients can have the same name or date of birth. A Patient-ID would provide a way to uniquely identify each patient, and no two patients would have the same ID.

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
Support