MySQL constraints
Constraint
Constaints define a specific rules, or limits , that we define in our tables
- The roll of constraints is to outline the existing replationships between deffirent tables in our database.
e.g : NOT NULL
PRIMARY KEY CONSTRAINTS :
Drop the “customers” table and re-create it using the following code:
CREATE TABLE customers
(
customer_id INT,
first_name varchar(255),
last_name varchar(255),
email_address varchar(255),
number_of_complaints int,
primary key (customer_id)
);
Then, create the “items” table
(columns - data types:
item_code – VARCHAR of 255,
item – VARCHAR of 255,
unit_price – NUMERIC of 10 and 2,
company_id – VARCHAR of 255),
and the “companies” table
(company_id – VARCHAR of 255,
company_name – VARCHAR of 255,
headquarters_phone_number – integer of 12).
DROP TABLE customers;
CREATE TABLE customers
(
customer_id INT,
first_name varchar(255),
last_name varchar(255),
email_address varchar(255),
number_of_complaints int,
primary key (customer_id)
);
CREATE TABLE items
(
item_code varchar(255),
item varchar(255),
unit_price numeric(10,2),
company_id varchar(255),
primary key (item_code)
);
CREATE TABLE companies
(
company_id varchar(255),
company_name varchar(255),
headquarters_phone_number int(12),
primary key (company_id)
);
FOREIGN KEY CONSTRAINS :
A foreign key in SQL defined through foreign key constraints.
The foreign key maintains the referencial integrity with in the database.
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.
To add foreign key : foreign key(column_name) references table_name(column_name) on delete cascade;
To add foreign key in existing table : alter table sales add foreign key (customer_id) references customers(customer_id) on delete cascade;
To drop foreign key in existing table : alter table sales drop foreign key foreign_key_name;
Looking to the next few lectures of the course, drop all tables from the “Sales” database in the following order: first drop “sales”, then “customers”, “items”, and finally “companies”.
DROP TABLE sales;
DROP TABLE customers;
DROP TABLE items;
DROP TABLE companies;
UNIQUE CONSTRAINS
Used whenever you would like to specify that you don't want to see duplicate data in given field.
It ensures that all values in column(or a set columns) are different.
To add the Unique key : we have to add UNIQUE KEY(column_name);
To add the Unique key in existing table : alter table table_name add unique key(column_name);
To remove the unique key : alter table table_name drop index Unique_key_column_name;
Unique keys are implemented in SQL through a constraints-the UNIQUE constraint.
Drop the “customers” table, and then recreate it using the following code.
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT,
first_name VARCHAR(255),
last_name VARCHAR(255),
email_address VARCHAR(255),
number_of_complaints INT,
PRIMARY KEY (customer_id)
);
Then run the following code that will add a “gender” column in the “customers” table, and will then insert a new record in it. Don’t worry if you don’t understand the meaning of the code perfectly – we will discuss these structures later on in the course in more detail. We will just use them now to insert a row in our “customers” table.
ALTER TABLE customers
ADD COLUMN gender ENUM('M', 'F') AFTER last_name;
INSERT INTO customers (first_name, last_name, gender, email_address, number_of_complaints)
VALUES ('John', 'Mackinley', 'M', 'john.mckinley@365careers.com', 0)
;
ANSWER :
create table customers(
customer_id int not null auto_increment,
first_name varchar(255),
last_name varchar(255),
email_address varchar(255),
number_of_complaints int,
primary key(customer_id)
);
alter table customers
add unique key (email_address);
alter table customers
drop index email_address;
drop table customers;
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT,
first_name VARCHAR(255),
last_name VARCHAR(255),
email_address VARCHAR(255),
number_of_complaints INT,
PRIMARY KEY (customer_id)
);
ALTER TABLE customers
ADD COLUMN gender ENUM('M', 'F') AFTER last_name;
INSERT INTO customers (first_name, last_name, gender, email_address, number_of_complaints)
VALUES ('John', 'Mackinley', 'M', 'john.mckinley@365careers.com', 0);
select * from customers;
DEFAULT CONSTRAINTS
Helps to assign a perticular default value to every row of a column.
-a value different from the default can be in a field where the default constraint has been applied , only if specifically indicated.
To add the default value :
column_name datatype default value;
To add default value in existing table :
alter table table_name change column column_name same_column_name data_type default value;
To drop the default value from existing table :
alter table table_name
alter column column_name drop default;
Recreate the “companies” table
(company_id – VARCHAR of 255,
company_name – VARCHAR of 255,
headquarters_phone_number – VARCHAR of 255),
This time setting the “headquarters phone number” to be the unique key, and default value of the company's name to be “X”.
After you execute the code properly, drop the “companies” table.
CREATE TABLE companies
(
company_id VARCHAR(255),
company_name VARCHAR(255) DEFAULT 'X',
headquarters_phone_number VARCHAR(255),
PRIMARY KEY (company_id),
UNIQUE KEY (headquarters_phone_number)
);
DROP TABLE companies;
NOT NULL Constraints
Using ALTER TABLE, first add the NULL constraint to the headquarters_phone_number field in the “companies” table, and then drop that same constraint.
ALTER TABLE companies
MODIFY headquarters_phone_number VARCHAR(255) NULL;
ALTER TABLE companies
CHANGE COLUMN headquarters_phone_number headquarters_phone_number VARCHAR(255) NOT NULL;
note : 0 & none are provided by the user where as null is assigned by the system.
Comments
Post a Comment