(click anywhere to close)
OPEN MENU

[SQL] Keys & Constraints

category: Website | course: SQL | difficulty:

Continuing on last chapter, we’ll be talking about the rest of the (more advanced) keys and constraints. These are: auto increment, unique, check and foreign keys.

Auto Increment

The auto increment constraint is typically used on the primary key. It does exactly what it says: it automatically increments the value of a certain column for you. Whenever you insert a new entry, it checks the last value of the auto increment column, and inserts (last_value + 1) for the new entry.

This saves you lots of time and energy, as you don’t need to keep track of the last value of the column yourself, and all values in the column are automatically unique.

Most database systems also let you set the starting value of the column, and allow larger increments than 1. For example, you could add 1000 every time, if that’s what you needed.

The syntax is:

column_name type AUTO_INCREMENT ALTER TABLE someTable AUTO_INCREMENT=startMySQL column_name type AUTOINCREMENT(start, interval)SQLite
PostgreSQL
MS Access
column_name type IDENTITY(start, interval)SQL Server
CREATE TABLE posts
	(id    INT(11) PRIMARY KEY AUTO INCREMENT,
	 title VARCHAR(255),
	 post  LONGTEXT);

-- Notice how we don't insert an id
INSERT INTO posts (title, post) VALUES ("Panda captured.", "Today, we captured a panda. He is safe now.");
INSERT INTO posts (title, post) VALUES ("PANDA ESCAPED!", "Oh no, our pet panda has escaped and is now wreaking havoc in our cities!");
INSERT INTO posts (title, post) VALUES ("Tiger captured.", "Today, we captured a tiger. it is safe now. What can go wrong?");
idtitlepost
1Panda captured.Today, we captured a panda. He is safe now.
2PANDA ESCAPED!Oh no, our pet panda has escaped and is now wreaking havoc in our cities!
3Tiger captured.Today, we captured a tiger. it is safe now. What can go wrong?

Oracle doesn’t support a simple auto increment feature – instead, you can emulate it using sequences, which will be discussed later.

Unique

As the name suggests, the UNIQUE keyword forces a column to only contain unique values – no two values may be the same, and if you try to insert an entry with an already existing value, it will throw errors and crash.

column_name type attributes, UNIQUE (column_name)MySQL column_name type UNIQUEOthers
-- We demand every user has a username, and that username is unique
CREATE TABLE users
	(id       INT(11) PRIMARY KEY AUTO INCREMENT,
	 username VARCHAR(255) UNIQUE NOT NULL);

INSERT INTO posts (username) VALUES("Juan");
INSERT INTO posts (username) VALUES("Panda");

-- Not allowed.
INSERT INTO posts (username) VALUES("Juan");
idusername
1Juan
2Panda

You can also create a unique constraint over multiple columns. This means that the combination of values must be unique across the table, not that those values itself must be different. For example, if one entry has values (2,2,3) and another has (2,3,2), they will be unique. If they both have (2,2,3), they are not.

The syntax is:

CONSTRAINT someName UNIQUE (column_1,, column_n)

Check

The CHECK constraint simply checks whether a certain condition is true when inserting a new entry. If it’s true, everything’s fine. If not, the entry is rejected. You can use this, for example, to make sure somebody can’t place a negative amount of orders on your web shop.

The syntax is:

column_name type attributes, CHECK (condition)MySQL column_name type CHECK (condition)Others
-- Our website has games with violence, so only people over 18 are allowed!
CREATE TABLE users
	(id       INT(11) PRIMARY KEY AUTO INCREMENT,
	 username VARCHAR(255) UNIQUE NOT NULL,
	 age      INT(3) CHECK (age >= 18));

INSERT INTO posts (username) VALUES("Juan", 20);
INSERT INTO posts (username) VALUES("Panda", 24);

-- Not allowed.
INSERT INTO posts (username) VALUES("Ginty", 16);
idusernameage
1Juan20
2Panda24

Again, to place a check on multiple columns at the same time, use

CONSTRAINT someName CHECK (condition_1 AND condition_2 AND );
-- After numerous complaints, we added a kids section to our website. Now people who are over 18, or who are a kid, can register.
CREATE TABLE users
	(id       INT(11) PRIMARY KEY AUTO INCREMENT,
	 username VARCHAR(255) UNIQUE NOT NULL,
	 type	  VARCHAR(10),
 	 age      INT(3),
	 CONSTRAINT ageCheck CHECK (age >= 18 OR type = 'Kid'));

INSERT INTO posts (username) VALUES("Juan","Adult",20);
INSERT INTO posts (username) VALUES("Panda","Adult",24);

-- Now it is allowed
INSERT INTO posts (username) VALUES("Ginty","Kid",16);
idusernametypeage
1JuanAdult20
2PandaAdult24
3GintyKid16

I will explain the AND and OR operators, and many more things about conditions, in the later chapters about reading from the database.

Foreign Keys

As you might have noticed by now, keywords and concepts in SQL are named quite intuitively. If you know the name, you know the syntax and the idea behind it. Foreign keys are no different.

A column that is set to be a foreign key, only contains references to the primary key of another table. In other words, such a column contains the primary keys from a foreign table.

Foreign keys are useful for normalizing your database (remember I introduced that term at the end of last chapter?). Instead of inserting all the actual data inside a table, you simply insert the primary key value of an entry from another table. Then, if you need the actual data, just select the right entry from the other table (with another query).

Because foreign keys reference primary keys, they are automatically never NULL, and they always reference exactly one entry (because of uniqueness). They do not, however, need to be unique themselves. For example, say you have a table (for a web shop) that holds the orders that have been placed, and a table that holds all users. It’s perfectly possible, then, that one user places multiple orders, and thus the orders table has multiple identical values for the user column.

The syntax for foreign keys is:

column_name type attributes, FOREIGN KEY (column_name) REFERENCES Table(foreign_column)MySQL column_name type FOREIGN KEY REFERENCES Table(foreign_column)Others
-- Our post_id references the primary key of the posts table (used at the start of this chapter), so that we know which post a certain comment belongs to.
CREATE TABLE comments
	(id       INT(11) PRIMARY KEY AUTO INCREMENT,
	 comment  VARCHAR(255),
	 post_id  INT(11) FOREIGN KEY REFERENCES posts(id));

INSERT INTO comments (comment, post_id) VALUES ("Dude, you really need to keep those pandas from escaping.", 2);
INSERT INTO comments (comment, post_id) VALUES ("Cool, a tiger!", 3);
INSERT INTO comments (comment, post_id) VALUES ("Your panda ate my crops!", 2);
idcommentpost_id
1Dude, you really need to keep those pandas from escaping.2
2Cool, a tiger!3
3Your panda ate my crops!2

Again, you can define a foreign key on multiple columns at the same time. This is needed if your primary key was also created out of multiple columns. The syntax is:

CONSTRAINT someName FOREIGN KEY (column_1,, column_n) REFERENCES Table(foreign_column_1,, foreign_column_n);
CONTINUE WITH THIS COURSE
Do you like my tutorials?
To keep this site running, donate some motivational food!
Crisps
(€2.00)
Chocolate Milk
(€3.50)
Pizza
(€5.00)