SQL basic syntax-Create tables and constraints

Create a database table

 UseSQL2016IF object_id('dbo. Employees','U') is  not NULLDROP TABLEdbo. Employees;Create TABLEdbo. Employees (EmpidINT  not NULL, FirstNameVARCHAR( -) not NULL, LastNameVARCHAR( -) not NULL, Salary Money Null); UseSQL2016IF object_id('dbo. Orders','U') is  not NULLDROP TABLEdbo. Orders;Create TABLEdbo. Orders (OrderIDINT  not NULL, EmpidINT  not NULL, CustomerIDVARCHAR( -) not NULL, QtyINT  not Null, Orderdatetime DATETIME2 not NULL,CONSTRAINTPk_orders--Create a PRIMARY KEY constraint directly when creating a tablePRIMARY KEY(OrderID));

--table already exists, create PRIMARY KEY constraintALTER TABLEdbo. EmployeesADD CONSTRAINTpk_employeesPRIMARY KEY(empid);--UNIQUE ConstraintALTER TABLEdbo. EmployeesADD CONSTRAINTUnq_employees_lastnameUNIQUE(lastname);--FOREIGN KEY ConstraintsALTER TABLEdbo. OrdersADD CONSTRAINTfk_orders_employeesFOREIGN KEY(Empid)REFERENCESdbo. Employees (empid);--Check ConstraintALTER TABLEdbo. EmployeesADD CONSTRAINTchk_employees_salaryCHECK(Salary>0.00);--Default ConstraintsALTER TABLEdbo. OrdersADD CONSTRAINTdft_orders_ordersDEFAULT(Sysdatetime ()) forOrderdatetime

Create test data

--Create test Data--The following two-paragraph script creates two employee Use [SQL2016]GOINSERT  into [dbo].[Employees]([Empid],[FirstName],[LastName],[Salary])VALUES(1,'Zhang','San', the)GOINSERT  into [dbo].[Employees]([Empid],[FirstName],[LastName],[Salary])VALUES(2,'Li','si',6000)GO --The following script creates two orders Use [SQL2016]GOINSERT  into [dbo].[Orders]([OrderID],[Empid],[CustomerID],[Qty])VALUES(1,1,'CUS1',Ten)GOINSERT  into [dbo].[Orders]([OrderID],[Empid],[CustomerID],[Qty])VALUES(2,2,'CUS2', -)GO

Verify constraint conditions

Validation of FOREIGN KEY constraints, Empid is 3 of the employee does not exist, so the following pin this newspaper wrong

 Use [SQL2016]GOINSERT  into [dbo].[Orders]([OrderID],[Empid],[CustomerID],[Qty])VALUES(3,Ten,'CUS1',Ten)GO

Validation of FOREIGN KEY constraints, deletion of primary table records, values in the primary table referenced by other tables, Empid=1 records referenced in order, delete error

 Use [SQL2016] DELETE  from WHERE empid=1GO

Verify FOREIGN KEY constraint, update main table record, value in Main table is referenced by other table, empid=1 record is referenced in order table, update failed

 Use [SQL2016] UPDATE set empid=4where empid=1GO

FOREIGN KEY constraints default to no action reference operation, that is, if the delete and update data are referenced, the operation is not allowed by default.

Other options:
CASCADE: Cascade Related rows
Set default: Sets the related rows as the defaults
Set NULL: Sets the related row to null

After you change delete rule and update rule to cascade.

Execute the following script to change empid=1 to 4 and delete the empid=2 record. You should automatically change the empid=1 in orders to 4 and delete empid=2 orders.

 Use [SQL2016] GO UPDATE set empid=4where empid=1DELETEfrom  WHERE empid=2GOSELECT* from Dbo. Orders

The CHECK constraint rejects the operation only when false, when True and Unknow are allowed, such as
-1000, Reject action
5000, allow operation
NULL, allow operation
With NOCHECK option:

SQL basic syntax-Create tables and constraints