CONSTRAINTS
Every columns in a table that we create should be administered. It should follow some rules that ensures the reliability of the data. Constraints in SQL provides such etiquette to the data tables.
Some of the common constraints that are used in SQL are:
It is used to prevent a column in a table to have NULL values
A column cannot have repeated values when this constraint is applied.
It is used to identify each row in a table uniquely.
PRIMARY KEY = UNIQUE + NOT NULL constraints
It is used to identify each row in a table or a different table uniquely. It creates a relation between tables.
It provides a constraint in a column value with a specific condition.
It provides a default value to a column if nothing is inserted.
SYNTAX
CREATE TABLE table_name (
column_name1 datatype constraint,
column_name2 datatype constraint,
...
);
Lets start learning about different constraints with an example. You can follow along with the examples and at the end of this tutorial you will be able to:
Create Database
Create Table
Alter Table
Use Constraints
We will be creating two tables. Table 1 (Students) contains information about students and Table 2 (Games) contains the information about the games that the students have participated in.
First of all, let us create a database sql_practice to store our tables in.
CREATE DATABASE sql_practice;
USE DATABASE sql_practice;
Create Table Students
Now, using following syntax, we can create tables, Stuents and Games.
CREATE TABLE students (
student_id INT NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email varchar(255)
);
CREATE TABLE games (
game_id INT NOT NULL,
game_name varchar(255) NOT NULL,
participant varchar(255),
student_id INT
);
If you look at the statements of creating table above, you can find the use of constraint, NOT NULL, in each new columns we are adding into the table. To learn more about NOT NULL, lets move to this section: