SQL Server constraints and operations on constraints and structs

I. Constraints

Data integrity: Correct, consistent data.

Designer: Before data table creation

To achieve data integrity:

1. Entity integrity: (row unique does not repeat)
Role: Ensure that rows are unique and not duplicated
Technical means: PRIMARY KEY constraint primary key (PK)
* * The table can have no primary key, but unless the special case table has a primary key, it guarantees entity integrity.
* * Features: cannot be duplicated, cannot be null (NULL), a table can have only one primary key, but may be a federated primary key
* * The primary key can be modified, but after the change to ensure that the only non-repetition.

Unique value constraint unique (UQ)
**unique unique value constraint (important supplement to primary key primary key)
* * Effect: Row unique does not repeat
* * A table can have multiple unique
**unique is allowed to be null, but only one behavior can be null

Assistive Technology: Identifying attribute Identity
* * Identity attribute (generate AutoNumber)
* * Read Only
* * A table can have only one identity attribute
* * Plus in integer type
* * New data will be generated only forward, unless rebuilding

Global Unique identifier GUID
* * Generate function New ID

2. Domain integrity: (column)
Function: Limit the range, format, and options of a column
Technical means: Data type age varchar (50)
Null is allowed for null,not null must be filled
Check constraint check (CK)
Default constraint defaults (DF)

**and (with) or (or) not (non)
Like wildcard characters
**%: Any character, any number
**_: Any character, one character, ' Zhang _ ' id has a character behind it
**[]: Within range, [0-9]
**[^]: Not in range [^0-5]

3. Referential integrity: (between two tables)
Role: To ensure consistency of data between two tables by establishing a relationship between the related fields of two tables
Example:
Use practice
CREATE TABLE Studentinfo
(
Id int PRIMARY KEY,
Name varchar (20),
Age int Check (AGE>=18 and age<=60)
)
CREATE TABLE Score
(
[Subject] varchar (50),
ID int foreign key references studentinfo (ID)
--Two related fields for establishing a relationship
--names can be different but types and lengths must be the same
)
Technology: FOREIGN key foreign key (FK)
* * Principle of Master-slave table:
* * Only data that exists in the primary table can be added to the From table
Law:
When added: Guthrie table, back from table
Delete: First from the table, the rear table

*************************************************************************************************************** *******

Two. Structure and constraint operations

Modify Structure
--1. Structural modifications
--Add columns
ALTER TABLE test--Modify the tables Test
Add [address] varchar (+) not null--added address column

--Modify Column column type, length, empty or non-empty
ALTER TABLE test--Modify the tables Test
Alter column [Address]nvarchar (null--) Modify columns

--Delete Columns
ALTER TABLE test--Modify the tables Test
Drop Column [address]--delete columns [column name]

--2. Modifying constraints
Add constraint
ALTER TABLE test--modifying tables
Add CONSTRAINT pk_test_id--Adding a PRIMARY key constraint to pk_test_id
Primary key (ID)--specific constraint conditions
--Delete Constraint

Practice:
--Practice:
--1 adds a check constraint for the age field of the test table, which requires adulthood.
--2 Add a Birthday field.
--3 The default value for setting birthdays is: 1980-01-01.
--4 modifies the test table, requiring the Name column to be unique.

--EG1:
ALTER TABLE Test
ADD CONSTRAINT Ck_test_age
CHECK (age>=18)
--EG2:
ALTER TABLE Test
ADD [Birthday]nvarchar (+) NULL
--eg3
ALTER TABLE test--modifying tables
Add CONSTRAINT df_test_birthday--Adding constraints
Default ' 1980-01-01 ' for birthday--defaults to XXXX for field
--eg4
ALTER TABLE test--modifying tables
Add CONSTRAINT uq_test_name--Adding unique value constraints
Unique (name)-Unique value (field name)

*************************************************************************************************************** *******

Three. DML ([data] manipulation language)
--sql (Basic skills)

--1. INSERT add
--Syntax:
--insert [into] tab_name[(colname,colname2 ... List of columns)]
--values (Value1,value2 ... The list of values matches the list of columns),
--(), () add more than one line at a time

--EG1: (Add data to all columns)
--1.
INSERT into info (sname,sex,birthday,address,tel)
VALUES (' Wol ', 0, ' 1982-3-6 ', ' Jilin ', ' 0432-12345678 ')
--2.
INSERT info VALUES (' Jaycee name ', 1, ' 1980-2-25 ', ' Beijing ', ' 010-123123123 ')
SELECT * FROM Info

INSERT into info (sname,sex,birthday,address,tel)
VALUES (' Leopard ', 1, ' 1970-5-9 ', ' Tianjin ', ' 13412362322 ')

INSERT info VALUES (' Zhaoyue ', 0, ' 1988-4-26 ', ' Changsha ', ' 03318658888 ')
SELECT * FROM Info
--Add some column data
--1.
INSERT into info (sname,birthday,tel)
VALUES (' JACK ', ' 1999-02-04 ', ' 1888888888 ')
--2.
INSERT into info
VALUES (' JACK ', null, ' 1999-02-04 ', null, ' 01088888888 ')
UPDATE info SET tel= ' 18888888888 ' WHERE tel= ' 01088888888 '

*************************************************************************************************************** *******

SQL Server constraints and operations on constraints and structures