MySQL Constraints
PRIMARY KEY Constraint
PRIMARY KEY Constraint
So far:
PRIMARY KEY Constraint
So far:
- Theory of Relational Databases
PRIMARY KEY Constraint
So far:
- Theory of Relational Databases
- SQL Theory
PRIMARY KEY Constraint
So far:
- Theory of Relational Databases
- SQL Theory
- Creating a Database
PRIMARY KEY Constraint
So far: In this section:
- Theory of Relational Databases
- SQL Theory
- Creating a Database
PRIMARY KEY Constraint
So far: In this section:
- Theory of Relational Databases - Constraints
- SQL Theory
- Creating a Database
PRIMARY KEY Constraint
So far: In this section:
- Theory of Relational Databases - Constraints
- SQL Theory In this lesson:
- Creating a Database
PRIMARY KEY Constraint
So far: In this section:
- Theory of Relational Databases - Constraints
- SQL Theory In this lesson:
- Creating a Database
- the PRIMARY KEY Constraint
PRIMARY KEY Constraint
constraints
specific rules, or limits, that we define in our tables
PRIMARY KEY Constraint
constraints
specific rules, or limits, that we define in our tables
- the role of constraints is to outline the existing
relationships between different tables in our database
PRIMARY KEY Constraint
constraints
specific rules, or limits, that we define in our tables
- the role of constraints is to outline the existing
relationships between different tables in our database
e.g. NOT NULL
PRIMARY KEY Constraint
Sales
FOREIGN KEY Constraint
FOREIGN KEY Constraint
Sales
FOREIGN KEY Constraint
Sales
FOREIGN KEY Constraint
foreign key
points to a column of another table and, thus, links the two tables
FOREIGN KEY Constraint
FOREIGN KEY Constraint
child table
FOREIGN KEY Constraint
parent table
child table
FOREIGN KEY Constraint
parent table
child table
FOREIGN KEY Constraint
parent table = referenced table
child table = referencing table
FOREIGN KEY Constraint
Sales
FOREIGN KEY Constraint
Sales
FOREIGN KEY Constraint
Sales
FOREIGN KEY Constraint
Sales
FOREIGN KEY Constraint
Sales
Remember, this is not an obligatory requirement – these two keys may
have two completely different names. What’s important is that the data
types and the information match! It’s just common practice to use, if
not the same, then similar names for both keys.
FOREIGN KEY Constraint
Sales
parent table = referenced table
child table = referencing table
FOREIGN KEY Constraint
a foreign key in SQL is defined through a foreign key
constraint
FOREIGN KEY Constraint
a foreign key in SQL is defined through a foreign key
constraint
the foreign key maintains the referential integrity within the database
FOREIGN KEY Constraint
ON DELETE CASCADE
if a specific value from the parent table’s primary key has been
deleted, all the records from the child table referring to this value
will be removed as well
FOREIGN KEY Constraint
FOREIGN KEY Constraint
FOREIGN KEY Constraint
FOREIGN KEY Constraint
ON DELETE CASCADE
UNIQUE Constraint
UNIQUE Constraint
unique key
used whenever you would like to specify that you don’t want to see
duplicate data in a given field
UNIQUE Constraint
unique key
used whenever you would like to specify that you don’t want to see
duplicate data in a given field
- ensures that all values in a column (or a set of columns)
are different
UNIQUE Constraint
unique keys are implemented in SQL through a constraint –
the UNIQUE Constraint
UNIQUE Constraint
unique keys are implemented in SQL through a constraint –
the UNIQUE Constraint
if you attempt to insert an already existing, duplicate value in the
unique column, SQL will display an error
UNIQUE Constraint
unique keys are implemented in SQL through a constraint –
the UNIQUE Constraint
if you attempt to insert an already existing, duplicate value in the
unique column, SQL will display an error
ERROR
UNIQUE Constraint
Sales
UNIQUE Constraint
UNIQUE Constraint
Indexes
UNIQUE Constraint
Indexes
unique keys in MySQL have the same role as indexes
UNIQUE Constraint
Indexes
unique keys in MySQL have the same role as indexes
the reverse isn’t true !!!
UNIQUE Constraint
Indexes
unique keys in MySQL have the same role as indexes
the reverse isn’t true !!!
index of a table
an organizational unit that helps retrieve data more easily
UNIQUE Constraint
Indexes
unique keys in MySQL have the same role as indexes
the reverse isn’t true !!!
index of a table
an organizational unit that helps retrieve data more easily
- it takes more time to update a table because indexes must be updated, too, and
that’s time consuming
UNIQUE Constraint
UNIQUE Constraint
ALTER TABLE table_name
DROP INDEX unique_key_field;
DEFAULT Constraint
DEFAULT Constraint
DEFAULT Constraint
DEFAULT Constraint
DEFAULT Constraint
helps us assign a particular default value to every row of a column
DEFAULT Constraint
DEFAULT Constraint
helps us assign a particular default value to every row of a column
- a value different from the default can be stored in a field where the
DEFAULT constraint has been applied, only if specifically indicated.
DEFAULT Constraint
DEFAULT Constraint
CREATE TABLE customers
(
customer_id INT,
first_name VARCHAR(255),
last_name VARCHAR(255),
email_address VARCHAR(255),
number_of_complaints INT DEFAULT 0,
PRIMARY KEY (customer_id)
);
DEFAULT Constraint
DEFAULT Constraint
2
DEFAULT Constraint
2 Peter Figaro M
Data Definition Language
Data Definition Language (DDL)
- CREATE
- ALTER
- DROP
NOT NULL Constraint
NOT NULL Constraint
primary key unique key
NULL VALUES no yes
NOT NULL Constraint
primary key unique key
NULL VALUES no yes
the “not null” restriction is applied through the NOT NULL Constraint
NOT NULL Constraint
primary key unique key
NULL VALUES no yes
the “not null” restriction is applied through the NOT NULL Constraint
- when you insert values in the table, you cannot leave the respective
field empty
NOT NULL Constraint
primary key unique key
NULL VALUES no yes
the “not null” restriction is applied through the NOT NULL Constraint
- when you insert values in the table, you cannot leave the respective
field empty
- if you leave it empty, MySQL will signal an error
ERROR
NOT NULL Constraint
NOT NULL Constraint
NOT NULL
NOT NULL Constraint
CREATE TABLE companies
(
company_id INT AUTO_INCREMENT,
headquarters_phone_number VARCHAR(255),
company_name VARCHAR(255),
PRIMARY KEY (company_id)
);
NOT NULL Constraint
CREATE TABLE companies
(
company_id INT AUTO_INCREMENT,
headquarters_phone_number VARCHAR(255),
company_name VARCHAR(255) NOT NULL,
PRIMARY KEY (company_id)
);
NOT NULL Constraint
Don’t confuse a NULL value with the value of 0 or with a
“NONE” response!
Think of a null value as a missing value.
0 NONE NULL
assigned by the assigned by the
user computer
NOT NULL Constraint
NULL
the value
of 0,
NOT NULL
NOT NULL Constraint
NULL
NOT NULL Constraint
NULL
NOT NULL Constraint
NULL
