SQL_ Experiment 2.1.3 Tsinghua University Press

This article originates from: Http://blog.csdn.net/svitter

Experimental objectives: Familiar with entity integrity, referential integrity, transaction processing;

/*1. Creating table Stu_uion in database School table, PRIMARY KEY constraint, inserting and updating a record without violating entity Integrity */use School CREATE TABLE stu_uion (Sno char (5) NOT NULL Unique,sname Char (8), Ssex char (1), Sage Int,sdept char (a), constraint Pk_stu_uion primary key (SNO)); Insert Stu_uion VALUES (' 10000 ', ' wangmin ', ' 1 ', and ' CS '); update stu_uion Set sno = ' WHERE sdept = ' cs '; update stu_uion set sno = ' 9200 2 ' Where sname = ' wangmin '; select * from stu_uion;/*2.3. Demonstrates inserts that violate entity integrity, update operations */use Schoolinsert stu_uion values (' 10000 ', ' Li h UA ', ' 1 ',/*unique*/update, ' CS '); stu_uion set sno = NULL where sno = ' 10000 '; /*not Null*//*4. Demonstrates the processing of a transaction, including the establishment, processing, and fallback of an error transaction */use schoolset xact_abort on/* setting Xact_abort to ON, If an error occurs in the transaction (transaction) statement, the entire transaction will be rolled back * If set to OFF, only the error rollback statement */begin transaction t1insert into stu_uion values (' 95009 ', ' Li  Yong ', ' M ', +, ' ee '), insert into stu_uion values (' 95003 ', ' Wang Hao ', ' 0 ', ' "EE '), insert into stu_uion values (' 95005 ', ' Wang Hao ', ' 0 ', ' EE '); select * from Stu_uion; commit transaction t1/*5. Insert data by creating a scholarship table to demonstrate current and existing data environmentEntity integrity and referential integrity cannot be established when unequal */use schoolcreate table Scholarship (m_id varchar), stu_id char (TEN), R_money int); INSERT INTO Scholarship VALUES (' 0001 ', ' 700000 ', ', '), insert into scholarship values (' 0001 ', ' 800000 ', ', "); select * FROM Scholarsh Ip;/*constraint*/use schoolalter Table Scholarship addconstraint pk_scholarship primary key (m_id);/*pk:primary key*//* There are two 0001, the data in the PRIMARY KEY constraint *//**scholarship cannot be established, the SID correspondence in the STU_ID and students tables is not met, and referential integrity failures are created */use schoolalter table Scholarship AddConstraint fk_scholarship foreign KEY (stu_id) references students (SID);