Foreign key additions and deletions and search exercises

--about the primary foreign key exercises--adding and removing primary and foreign key syntax--deleting FOREIGN KEY syntax: ALTER TABLE name DROP constraint foreign KEY constraint name--Add foreign KEY syntax: ALTER TABLE NAME-constraint foreign KEY constraint name Forei GN key (column name) references Reference foreign key table (column name)--Delete primary KEY syntax: ALTER TABLE name DROP constraint PRIMARY KEY constraint name--Add PRIMARY KEY syntax: ALTER TABLE name ADD constraint PRIMARY KEY constraint name PRI Mary Key (column name)--delete primary key ALTER TABLE SC drop constraint pk_sc--delete foreign key ALTER TABLE SC DROP constraint Fk_studentalter table SC drop Constraint fk_course--creates a student table create TABLE student (Id int identity (Primary key,name varchar), age Int,sex char (1), Class varchar (6)); INSERT into student values (' p0001 ', ' Wang June ', 21,1, ' c101 ');  -(' p0001 ', ' Wang June ', 20,1, ' c101 '); INSERT into student values (' p0002 ', ' Tommy ', 21,1, ' c102 '); --(' p0002 ', ' Tommy ', 21,1, ' cl01 '); INSERT into student values (' p0003 ', ' Liu Fei ', 22,1, ' c103 '); INSERT into student values (' p0004 ' , ' Zhao Yan ', 18,0, ' c103 '); INSERT into student values (' p0005 ', ' Zeng Ting ', 19,0, ' c103 '); INSERT into student values (' p0006 ', ' Zhouhui ', 21,0 , ' c104 '); INSERT into student values (' p0007 ', ' Little Red ', 23,0, ' c104 '); INSERT into student values (' p0008 ', ' Yang Xiao ', 18,0, ' c104 '); I Nsert intoStudent values (' p0009 ', ' Li Jie ', 20,1, ' c105 '); INSERT into student VALUES (' p0010 ', ' Zhang Liang ', 22,1, ' c105 ');--Create a curriculum creating table  Course (ID int identity (Primary) key,--identity (total) starting from 1, each time self-increment 1sname varchar (each), credit numeric (2,1),--number type, overall length 2 = Integer length + fractional length. Retains 1 decimal places. Teacher varchar); INSERT into course values (' c001 ', ' Java ', 3.5, ' Miss Li '); INSERT into course values (' c002 ', ' Advanced math ', 5.0, ' Miss Zhao '); INSERT into course values (' c003 ', ' JavaScript ', 3.5, ' Miss Wang '); INSERT into course values (' C004 ', ' discrete math ', 3.5, ' Miss Bu '); Nsert into course values (' C005 ', ' database ', 3.5, ' teacher Liao '); INSERT into course values (' C006 ', ' operating system ', 3.5, ' Miss Zhang ');-- Create an elective table-the elective table is a relational or intermediate table that provides a mapping of student table to curriculum CREATE TABLE SC (SID varchar) not null,cid varchar (TEN) not NULL); ALTER TABLE SC ADD c Onstraint pk_sc Primary Key (SID,CID); --Add primary KEY Group ALTER TABLE SC add constraint fk_student Foreign key (SID) references student (ID); --Add FOREIGN KEY constraint ALTER TABLE SC add constraint fk_course Foreign key (CID) references course (ID);--Add foreign KEY constraint insert into SC values (' p000 1 ', ' c001 '); INSERT into SC values (' p0001 ',' c002 '); INSERT into SC values (' p0001 ', ' c003 '); INSERT into SC values (' p0002 ', ' c001 '); INSERT into SC values (' p0002 ', ' c004 INSERT into SC values (' p0003 ', ' c002 '), insert into SC values (' p0003 ', ' c005 '), insert into SC values (' p0004 ', ' C003 '); Nsert into SC values (' p0005 ', ' C001 '), insert into SC values (' p0006 ', ' C004 '), insert into SC values (' p0007 ', ' C002 '), insert into SC values (' p0008 ', ' C003 '), insert into SC values (' p0009 ', ' C001 '), insert into SC values (' p0009 ', ' C005 ');-- Query for students with no classes and no student-selected course information Select *from Student where Student.id not in (select Sid from SC); Select *from Course where course.id Not in (select CID from SC); SELECT student.* from student left join SC in Student.id=sc.sid LEFT join course on course.id = Sc.cid WHERE course.sname is NULL; SELECT course.* from course left join SC in Course.id=sc.cid left join student on student.id = Sc.sid WHERE student.sname is null;--drop table student;drop table Course;drop table SC;

--The above exercise material from the Internet

Foreign key additions and deletions and search exercises