Constraints in SQL: What that Means and What Common Constraints to Use

ConstraintsinSQLSo you’re interested in learning about constraints? Are you moseying your way through SQL 101? Curious about what it takes to program? It’s time to understand what this fundamental piece of SQL means and how it can help you accomplish the things you want to accomplish while programming. In this SQL server training you can learn a lot more than just what constraints are!

First, let’s understand exactly what SQL is. SQL stands for Structured Query Language, in other words, it’s a specialized programming language that communicates with databases. SQL is the language used to create or manage databases and specifies all of the data held within. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

An SQL statement might look like this:

select "column1"
[,"column2",etc]
from "tablename"
[where "condition"];
[] = optional

A series of statements will tell the database what to do and how to act.  Now is where we need include constraints. Constraints are the rules enforced on data columns of a table. We use constraints to limit the type of data that can go into a table. This helps ensure the accuracy and reliability of the data in the database, which, is of course really important. Find this stuff really interesting? Learn all about SQL in 6 days!

A constraint command may look something like this:

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

Constraints can be written for the column or table level. This basically means that column level constraints are applied to only one column, and table level constraints are, you guessed it, applied to the whole table. This gives you ultimate flexibility in managing your database.

Common Constraints

Let’s visit some commonly used constraints so you can get an idea as to what exactly can be accomplished with these types of commands.

  • DEFAULT Constraint

This constraint proves a default value for a column when you don’t have one specified. If you are trying to insert system values, you might use a command like this:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P-Id int,
OrderDate date DEFAULT GETDATE ()
)

The default value you set will be added to all new records you make, as long as no other value has been set.

  • UNIQUE Constraint

This constraint command will ensure that all values within a column will be different. So, no duplicates. Multiple UNIQUE constraints can be defined on a table. For example:

CREATE TABLE table_name
(
column1 datatype null/not null,
column1 datatype null/not null,
CONSTRAINT constraint_name UNIQUE(column1, 
column2, . column_n)
);
  • NOT NULL Constraint

This constraint makes sure that no matter what, a column cannot have a NULL value. By default, columns can hold NULL values. A sample of using NOT NULL in modifying an existing tables rules,  is below. Please note that a NOT NULL value is not the same as no data, is just means that the data value is unknown.

ALTER TABLE CUSTOMERS
MODIFY SALARY DECIMAL (18,2) NOT NULL;
  • PRIMARY Key (PK)

The PRIMARY Key will uniquely identify each row or record in a database table. This key is incredibly important to your relational database. Without it, there would be no way for your to create unique row identifiers. Make sure that you never drop the PRIMARY Key. If you do, you risk losing important data or causing data corruption. A sample of entering a PRIMARY Key is:

CONSTRAINT <constraint_name> PRIMARY KEY CONSTRAINT <constraint_name> PRIMARY KEY <primary_key_column>;

Fill in the “primary key column” with your primary key column name.

  • FOREIGN Key (FK)

You use the FOREIGN Key when you need to uniquely identify a row or record in another database table. Hence, foreign key, foreign database. A FOREIGN KEY points to a PRIMARY KEY in another table. The primary function of the FOREIGN KEY is to cross-references tables for you. The following SQL creates a FOREIGN KEY on the “P_Id” column when the “Orders” table is created in MySQL:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

To alter a table already created in MySQL, SQL Server, Oracle, or MS Access use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
  • CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy certain conditions. This is your checks and balances system. If you set a value range for a column, the CHECK constraint will ensure that this is the only range of values in that column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Here’s an example of writing a CHECK constraint command when creating a table:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
  • INDEX

This is great command. You can use it to create and retrieve data from a database really quickly. When you define an INDEX constraint a ROWID will be assigned to each row before sorting out the data. To create an INDEX constraint create a table command, do something like this:

CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) NOT NULL,
SALARY DECIMAL (18,2),
PRIMARY KEY (ID)
);

Now you can create INDEX on multiple columns:

CREATE INDEX index-name
ON table_name (column1, column2, …);

Dropping Constraints

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

For example, to drop the primary key constraint in the USERS table, you can use the this command:

ALTER TABLE USERS DROP CONSTRAINT USERS_PK;

So, now you have a general idea of how some common constraints are used and how they can help you customize your database. Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statement to create or edit table constraints in a table that’s already been created.

To learn more about SQL using MySQL, Oracle, MS Access or SQL Server’s take a few minutes to check out SQL database training.