SQL Server Data integrity

we often hear two concepts in the process of learning a database: Data redundancy and data integrity. Data redundancy refers to the existence of some duplicate data in the database, data integrity refers to the data in the database can correctly respond to the actual situation, we say that in order to performance, the database allows some data redundancy, but to maintain the integrity of the data. Data integrity=Reliability+accuracy, here's what we need to know: • Data is stored in a table • "The problem with data integrity is mostly due to design" • When creating a table, you should ensure that data input is correct later-wrong data, non-conforming requirements Data does not allow input in nature to guarantee the integrity of the data=Enforcing integrity constraint integrity includes1. Entity integrity a) data rows cannot exist duplicates2. Domain integrity a) implements a limit on the values entered into a particular column3.              Referential integrity a) requires that the related item in the child table must exist in the primary table B) if the relationship between the primary table and the child table is established: I.              The data of the related items in the child table must exist in the main table;                     When the data for a related item in the primary table changes, the data item corresponding to the child table should also follow the third.              Change, IV. The primary table cannot be deleted until the child table is deleted;4. Custom Integrity In fact, the process by which SQL Server creates tables is the process of defining the properties of the data columns, as well as guaranteeing the implementation of data integrity, including entity integrity, referential integrity, and domain integrity. Creating a table includes selecting the field name, data type, whether the definition is empty, setting default values, primary key and foreign key relationships, checking constraints, and so on. There is no appropriate column in the table as the primary key, and you can create an identity column. There are five constraints in SQL Server: • The purpose of the constraint: to ensure the complete type of data in the table • Common constraint type: – PRIMARY KEY constraint (Primary Key Constraint): Requires primary key column data to be unique and is not allowed to be NULL – UNIQUE constraint (Unique Constraint): This column is required to be unique, allowed to be empty, but only one null value can appear. – Check Constraints (Check Constraint): A column value range limit, formatting restrictions, and so on, such as constraints on age-default constraints (Default Constraint): The default value of a column, such as our male students more, the gender default is "male" – FOREIGN KEY constraint (Foreign Key Constraint): For establishing a relationship between two tables, you need to specify the syntax for adding constraints to the column that references the main table:ALTER TABLETable nameADD CONSTRAINTConstraint name constraint type specific constraint description The name rule of the Q constraint is recommended to use: Constraint type _ constraint field Q primary KEY (Primary Key) Constraints: such as Pk_stunoq unique (Unique Key) Constraints: such as UQ_STUIDQ default (Default Key) Constraints: such as DF_STUADDRESSQ check (Check Key) constraint: such as Ck_stuageq foreign key (Foreign Keyconstraints: As Fk_stuno below we will add constraints to the Student Information table Stuinfo created earlier:/*Add PRIMARY KEY constraint (Stuno as primary key)*/ALTER TABLEStuinfoADD CONSTRAINTPk_stunoPRIMARY KEY(Stuno)/*add unique constraint (because each person's national ID number is unique)*/ALTER TABLEStuinfoADD CONSTRAINTUq_stuidUNIQUE(Stuid)/*Add a DEFAULT constraint (the default is "address unknown" if the address is not filled in)*/ALTER TABLEStuinfoADD CONSTRAINTDf_stuaddressDEFAULT('Address Unknown') forstuaddress/*Add checking check constraint, requires age only between 15-40 years old*/ALTER TABLEStuinfoADD CONSTRAINTCk_stuageCHECK(Stuagebetween  the  and  +)/*Add a FOREIGN KEY constraint (main table stuinfo and build relationship from table Stumarks, associate field Stuno)*/ALTER TABLEStumarksADD CONSTRAINTFk_stunoFOREIGN KEY(Stuno)REFERENCESstuinfo (Stuno)GOIf you add a constraint incorrectly, we can also delete the syntax of the constraint delete constraintALTER TABLETable nameDROP CONSTRAINTConstraint Name Example: Remove the address default constraint in the Stuinfo tableALTER  TABLEStuinfoDROP  CONSTRAINTdf_stuaddress OK, with integrity constraints, data integrity is guaranteed, the data in the database will be able to correctly reflect the actual situation. Then we mentioned a master-slave table concept, some people may not understand, the following article we will introduce. 

SQL Server Data integrity