Lab 8
Learning objectives:
Learn to use PRIMARY KEY constraint.
Learn to use FOREIGN KEY constraint.
Learn to use AUTO INCREMENT field.
8.0 Preliminaries
Create a new database named as “Lab8”.
CREATE DATABASE Lab8;
Select “Lab8” database for use.
USE Lab8;
8.1 PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain
UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key, which
can consist of single or multiple columns (attributes).
PRIMARY KEY on CREATE TABLE
Create a table “Customer” with attributes of customer id, name, address, and date of birth and create
the primary key on “customer id” column.
CREATE TABLE Customer (
CustomerID INT NOT NULL,
CustomerFirstName VARCHAR(30),
CustomerLastName VARCHAR(30),
CustomerAddress VARCHAR(50),
CustomerEmail VARCHAR(30),
PRIMARY KEY (CustomerID)
);
Show the structure of “Customer” table.
DESCRIBE Customer;
PRIMARY KEY on ALTER TABLE
Create a table “SaleOrder” with attributes of id, total amount and order date.
CREATE TABLE SaleOrder (
SaleOrderID INT,
SaleOrderTotalAmount DECIMAL(11,2),
SaleOrderDate DATE
);
Add PRIMARY KEY constraint on “id” attribute of the ‘SaleOrder’ table.
ALTER TABLE SaleOrder
ADD PRIMARY KEY (SaleOrderID);
Show the structure of “SaleOrder” table.
DESCRIBE SaleOrder;
Insert two new records in “SaleOrder” table.
INSERT INTO SaleOrder (SaleOrderID, SaleOrderTotalAmount)
VALUES (1, 12.50);
INSERT INTO SaleOrder VALUES (1, 11.11, ‘2019-11-11’);
DROP a PRIMARY KEY Constraint
ALTER TABLE SaleOrder
DROP PRIMARY KEY;
Now, insert a new record in “SaleOrder” table.
INSERT INTO SaleOrder VALUES (1, 11.11, ‘2019-11-11’);
Show all records in “SaleOrder” table.
SELECT * FROM SaleOrder;
Remove “SaleOrder” table.
DROP TABLE SaleOrder;
8.2 FOREIGN KEY Constraint
A FOREIGN KEY is to link two tables together. FOREIGN KEY is attribute(s) in a table that refers
to the PRIMARY KEY in another table.
FOREIGN KEY on CREATE TABLE
Create a table “SaleOrder” with attributes of id, total amount and order date. Create primary key on
“sale order id” attribute and include “customer id” attribute as foreign key.
CREATE TABLE SaleOrder (
SaleOrderID INT NOT NULL,
SaleOrderTotalAmount DECIMAL(11,2),
SaleOrderDate DATE,
CustomerID INT,
PRIMARY KEY (SaleOrderID),
FOREIGN KEY (CustomerID) REFERENCES
Customer(CustomerID)
);
Show the structure of “SaleOrder” table.
DESCRIBE SaleOrder;
Remove “SaleOrder” table.
DROP TABLE SaleOrder;
Create a table “SaleOrder” with attributes of id, total amount and order date. Create primary key on
“sale order id” attribute.
CREATE TABLE SaleOrder (
SaleOrderID INT NOT NULL,
SaleOrderTotalAmount DECIMAL(11,2),
SaleOrderDate DATE,
CustomerID INT,
PRIMARY KEY (SaleOrderID)
);
FOREIGN KEY on ALTER TABLE
Create a FOREIGN KEY constraint by specifying name of “SaleOrder_FK” on “customer id”
attribute in “SaleOrder” table.
ALTER TABLE SaleOrder
ADD CONSTRAINT SaleOrder_FK
FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID);
DROP a FOREIGN KEY Constraint
Remove the FOREIGN KEY constraint from ‘SaleOrder’ table.
ALTER TABLE SaleOrder
DROP FOREIGN KEY SaleOrder_FK;
8.3 AUTO INCREMENT field
Auto-increment generates a unique number automatically when a new record is inserted into a table. It
is used for the primary key field, which starts from 1(default) and is increment by 1 for each new
record.
MySQL AUTO_INCREMENT keyword
Create a table “Product” with attributes of product id, name (required attribute), description, and price.
Use auto increment on “product id” attribute.
CREATE TABLE Product (
ProductID INT NOT NULL AUTO_INCREMENT,
ProductName VARCHAR(30) NOT NULL,
ProductDescription VARCHAR(100),
ProductPrice DECIMAL(8,2),
PRIMARY KEY (ProductID)
);
Insert new records in “Product” table.
INSERT INTO Product (ProductName, ProductPrice) VALUES
(‘Apple’, 3.52);
INSERT INTO Product (ProductName, ProductPrice) VALUES
(‘Orange’, 2.79);
Show all products from “Product” table.
SELECT * FROM Product;
To let the AUTO_INCREMENT sequence start with value of 50.
ALTER TABLE Product
AUTO_INCREMENT=50;
Insert new record in “Product” table.
INSERT INTO Product (ProductName, ProductPrice) VALUES
(‘Mango’, 9.99);
Show all products from “Product” table.
SELECT * FROM Product;
Insert new record in “Product” table.
INSERT INTO Product VALUES (3, ‘White Grapes’, ‘Local’, 5.99);
INSERT INTO Product VALUES (NULL, ‘Red Grapes’, ‘Imported’,
7.59);
INSERT INTO Product VALUES (99, ‘Coconut’, NULL, 8.42);
INSERT INTO Product (ProductName, ProductPrice) VALUES (‘Plum’,
2.79);
Show all products from “Product” table.
SELECT * FROM Product;
