www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Structured Query Language
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in the last lecture, we introduced the relational model, and associated concepts of primary keys and foreign keys
in this lecture, we will learn how to create a table with different types of constraints declared using SQL
SQL – An introduction
Structured Query Language
three standards: SQL-92 (SQL2), SQL-99 (SQL3), SQL:2003
different dialects of SQL exist with some differences in query syntax
current standard is SQL-99 – most commercial DBMSs implement something similar, but not identical to it
e.g. some queries that work fine under MySQL produce errors under PostgreSQL
SQL commands may be executed either from command line or as part of a program
Create or delete a table
create a table
CREATE TABLE Student
name CHAR(30),
address VARCHAR(200),
birthdate DATE
delete a table
DROP TABLE Student;
Alter a table
add a column
ALTER TABLE Student ADD (Course CHAR(20));
modify a column
ALTER TABLE Student MODIFY (Address CHAR(250));
note: CREATE, DROP and ALTER syntax applies to many database elements (as we shall see later)
Constraints
NOT NULL constraint
we may require a column to be NOT NULL
CREATE TABLE Student
name CHAR(30) NOT NULL,
address VARCHAR(200),
birthdate DATE
this means that the name column must not have a NULL value
UNIQUE constraint
we may require a specified column or set of columns to be UNIQUE
CREATE TABLE Department
DeptNo NUMBER(4) UNIQUE,
DeptName CHAR(20)
this means that all values in the DeptNo column have to be different
Specifying primary keys
we may specify a column or set of columns to be a PRIMARY KEY
CREATE TABLE Department
DeptNo NUMBER(4) PRIMARY KEY,
DeptName CHAR(20)
a primary key column is both UNIQUE and NOT NULL
note that a table is allowed to have only one primary key
Specifying composite keys
when a key consists of more than one attribute,
then it must be declared as a constraint
CREATE TABLE Enrol
StudentNo NUMBER(10),
CourseNo NUMBER(6),
CONSTRAINT Enrol_PK
PRIMARY KEY (StudentNo, CourseNo)
Declaring foreign keys
foreign keys are declared by using the FOREIGN KEY clause
CREATE TABLE Employee
EmpNo NUMBER(5) PRIMARY KEY,
EmpName CHAR(20),
Salary NUMBER(6),
DeptNo NUMBER(4),
FOREIGN KEY (DeptNo) REFERENCES Department (DeptNo)
Oracle will automatically ensure that any value in the DeptNo of the Employee table matches a value of DeptNo in the Department table
Enforcing referential integrity
assumption: R2 references R1
two ways to enforce referential integrity
the operation on R1 is restricted to the case
where there are no matching values in R2
otherwise, the operation is rejected
the operation on R1 cascades to those matching values in R2
other enforcement is possible
On UPDATE/DELETE restrict
this is the default action
CREATE TABLE Employee
EmpNo NUMBER(5) PRIMARY KEY,
EmpName CHAR(20),
DeptNo NUMBER(4),
FOREIGN KEY (DeptNo)
REFERENCES Department (DeptNo)
prevents update/deletion of a DeptNo value in the Department table if it has corresponding value(s) in the DeptNo column of the Employee table
On UPDATE/DELETE cascade
cascades changes made to the primary keys to the foreign keys too
CREATE TABLE Employee
EmpNo NUMBER(5) PRIMARY KEY,
EmpName CHAR(20),
DeptNo NUMBER(4),
FOREIGN KEY (DeptNo)
REFERENCES Department (DeptNo)
ON DELETE CASCADE
if a DeptNo value in the Department table is deleted, all its dependent value(s) in the DeptNo column of the Employee table are deleted
CHECK constraint
specifies conditions that values in a column or group of columns within a single table must satisfy
CREATE TABLE Parts
PartNo NUMBER(5) PRIMARY KEY,
Colour VARCHAR(20) CONSTRAINT Valid_Colour
CHECK (Colour In (‘Red’,’Blue’)),
MaxDis NUMBER(2) CONSTRAINT Valid_Discount
CHECK (MaxDis < 50)
this states that the part colour must be either red or blue and maximum discount must be less than 50%
constraint name
Management of constraints
constraints can be switched on/off as necessary
ALTER TABLE Employee DISABLE PRIMARY KEY;
ALTER TABLE Employee DISABLE CONSTRAINT DeptNo_FK;
ALTER TABLE Employee ENABLE PRIMARY KEY;
note: a constraint cannot be enabled if existing data in a table violates the constraint!
Management of constraints
constraints may also be modified after being declared
ALTER TABLE Department ADD UNIQUE (DeptName);
ALTER TABLE Employee DROP CONSTRAINT DeptNo_FK;
again, a constraint cannot be enabled if existing data in a table violates the constraint!
Insert data into a table
to enter a full tuple into a table:
INSERT INTO Student
VALUES (' ', '123 High Street', '01-Jan-1986');
it is possible to name columns:
INSERT INTO Student(Name, Address)
VALUES (' ', '123 High Street');
columns not named will have a NULL value inserted
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com