MySQL Crash Course

Explore

MySQL Crash Course

02-Database Constraints

When you create your own databases in MySQL you also have the choice of putting different kinds of restrictions on the type and kind of data that they can store. These constraints help with the integrity of data stored in the database and help in keeping the data clean and consistent.

Primary Keys

Primary keys are used for making sure each record in the table has a unique value that can be used to identify that record. Like the employee id number in the employee database of a company.

No two rows can have the same primary key and the ids are the most commonly used primary keys since they are recorded against the details of an employee. Primary keys can be of more than one column but then it wouldn't work as other values could be same in more than one row.

In order to set the id column as the primary key you'll need to define it when you're creating the table.

CREATE TABLE users {

user_id int

name varchar(20)

age int

primary key (user_id) }

The benefits of using primary keys are data integrity and the values cannot be null and also this causes MySQL to create an index for each record that speeds up data retrieval.

Foreign Keys

A foreign key is the primary key of another table that is used to define a relationship between the two tables. The user_id from the users table can be the foreign key of the complaints table so that you easily see which complaints are made by which user.

Just like primary keys, the foreign key needs to be defined when creating the table but it requires the table that its going to have a relation with to be created before it can be created so that there are no problems when referencing data.

The column that is being referenced will also need to stored in this table as well so it needs a column of its own as well in the table.

CREATE TABLE complaints {

complaint_id int

user_id int

complaint varchar(20)

primary_key (complaint_id)

foreign_key (user_id) references user(user_id)}

MySQL wont allow you to insert any data in the complaints table unless the foreign is correct. If the user_id that you're trying to enter doesn't exist in the users table then the transaction won't be completed.

MySQL also wont allow you to delete any user ids that are used in the complaints table as foreign keys from the users table since the records in the complaints table would not point to any user and that can mess the referential integrity of the database.

A table can have only one primary key but it can have multiple foreign keys.

Not Null

Null values in MySQL represent empty or undefined values. Null doesn't mean that the value of the column is zero or a space character. Null means that the value of the column doesn't exist.

In the users table we can allow for the address and the phone number to be null since most users don't like to enter too much information about them. However, the name, email, and password columns cannot be null since they are necessary values for creating an account so we add the not null constraint to these columns when creating the table.

CREATE TABLE users {

user_id int

name varchar(25) not null

email varchar(25) not null

password varchar(16) not null

phone int

address varchar(25) }

The column that is being defined as the primary key doesn't need to be defined as not null because MySQL adds this constraint on that column by default.

Unique Values

The unique constraint, when added to a column will not save a value in that column if the value already exists. This is used to verify email addresses when creating new user account on websites so that only one email can be associated with a single account.

CREATE TABLE users {

name varchar(25)

email varchar(25) unique }

Check Constraint

The check constraint allows you to make sure that a column can only store certain values or values that fall in a certain range.

CREATE TABLE users{

name varchar(25)

age int check (year between 1967 and 2002) }

Default

The default constraint allows you to set a default value for a selected column if there is no data inserted in the column.

CREAT TABLE users

name varchar(25) default 'John Doe'

age int

email varchar(25)

If the user don't provide a name then the default value of `John Doe` will be inserted against the record.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.