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.
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
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?");
|1||Panda captured.||Today, we captured a panda. He is safe now.|
|2||PANDA ESCAPED!||Oh no, our pet panda has escaped and is now wreaking havoc in our cities!|
|3||Tiger 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.
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");
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 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);
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);
I will explain the
OR operators, and many more things about conditions, in the later chapters about reading from the database.
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);
|1||Dude, you really need to keep those pandas from escaping.||2|
|2||Cool, a tiger!||3|
|3||Your 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);