Lecture 2 - code

-- example TD 2.1
CREATE TABLE `branch` (
 `branchNo` char(4) NOT NULL,
 `street` varchar(50) DEFAULT NULL,
 `city` varchar(20) DEFAULT NULL,
 `postcode` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`branchNo`)
);

CREATE TABLE `staff` (
 `staffNo` char(6) NOT NULL,
 `fName` varchar(20) DEFAULT NULL,
 `branchNo` char(4) DEFAULT NULL,
 PRIMARY KEY (`staffNo`),
 CONSTRAINT `FK_staff_branchNo` FOREIGN KEY (`branchNo`) REFERENCES `branch` (`branchNo`)
);

insert into branch values
('B001', 'city1', 'street1', 'postcode1'),
('B002', 'city2', 'street2', 'postcode2'),
('B005', 'city5', 'street5', 'postcode5');

insert into staff values ('S1', 'staff1', 'b001');
insert into staff values ('S2', 'staff2', 'B007');
insert into staff values ('S3', 'staff3', 'B001');
insert into staff values ('S4', 'staff4', 'B002');
insert into staff values ('S1', 'staff5', 'B002');

-- using BINARY
CREATE TABLE `branch_binary` (
 `branchNo` char(4) binary NOT NULL,
 `street` varchar(50) DEFAULT NULL,
 `city` varchar(20) DEFAULT NULL,
 `postcode` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`branchNo`)
);

CREATE TABLE `staff_binary` (
 `staffNo` char(6) NOT NULL,
 `fName` varchar(20) DEFAULT NULL,
 `branchNo` char(4) BINARY DEFAULT NULL, -- needed, otherwise FK will fail!
 PRIMARY KEY (`staffNo`),
 CONSTRAINT `fk` FOREIGN KEY (`branchNo`) REFERENCES `branch_binary`(`branchNo`) ON DELETE RESTRICT ON UPDATE RESTRICT

 -- FOREIGN KEY (`branchNo`) REFERENCES `branch` (`branchNo`)
);
insert into branch_binary values
('B001', 'city1', 'street1', 'postcode1'),
('b001', 'city1x', 'street1x', 'postcode1x');

-- example TD2.2
CREATE TABLE `branch` (
 `branchNo` char(4) NOT NULL,
 `street` varchar(50) DEFAULT NULL,
 `city` varchar(20) DEFAULT NULL,
 `postcode` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`branchNo`)
);

CREATE TABLE `staff` (
 `staffNo` char(6) NOT NULL,
 `fName` varchar(20) DEFAULT NULL,
 `branchNo` char(4) DEFAULT NULL,
 PRIMARY KEY (`staffNo`),
 CONSTRAINT `FK_staff_branchNo`
    FOREIGN KEY (`branchNo`)
    REFERENCES `branch` (`branchNo`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

insert into branch values
('B001', 'city1', 'street1', 'postcode1'),
('B002', 'city2', 'street2', 'postcode2'),
('B005', 'city5', 'street5', 'postcode5');

insert into staff values
('S1', 'staff1', 'b001'),
('S2', 'staff2', 'B001'),
('S3', 'staff3', 'B002'),
('S4', 'staff4', 'B002'),
('S5', 'staff5', 'B005');

delete from branch where branchNo = 'B001';
update branch set branchNo = 'B007' where branchNo = 'B005'

• DELETE 和 UPDATE 是数据操作语句，它们用于增删改表中的数据。

• ALTER 是表结构操作语句，用于更改表结构（例如，添加、修改或删除列和约束），而不是用于更改表中的数据。 

-- example TD 2.3, please run example 2.2 first

alter table staff add `lName` varchar(20) not null;
alter table staff drop column `lName`;
alter table staff change column `fName` `first_name` varchar(20) not null;
alter table staff modify column `first_name` varchar(40) not null;

alter table branch add constraint ck_branch unique (street);
alter table staff add constraint fk_staff_staff
    foreign key (branchNo) references branch (branchNo);

alter table staff drop primary key;
alter table staff drop foreign key fk_staff_staff;
alter table branch drop index ck_branch;


-- example TD 2.4
drop table branch; -- will fail, because of foreign key.

alter table staff drop foreign key FK_staff_branchNo;
drop table branch; -- will succeed now!