s1/c# Language and database technology fundamentals/07-using tables to organize data

SQL Server provides the following four types of constraints

1. Entity integrity constraints

Each row of data in the entity Integrity requirements table reflects a different entity and cannot have the same data row.

The entity integrity of a table can be achieved through indexes, unique constraints, PRIMARY key constraints, or identity column properties. Implementations of these methods are described later.

2. Domain Integrity Constraints

Domain integrity refers to the validity of a given column input.

The domain integrity of a table can be achieved by restricting data types, checking constraints, input formats, FOREIGN KEY constraints, default values, non-null constraints, and more.

3. Referential integrity Constraints

Referential integrity constraints are used to maintain defined relationships between tables when data rows are entered or deleted.

For example, when managing student information, a table is used to store student information, another table stores the details of the exam results, and a list of values in the test score table is the number in the Student information table that is used to indicate the student's information.

When referential integrity constraints are enforced,SQL Server prevents users from doing the following.

• When there is no associated record in the primary table, the record is added to the from table, which means that the student's score table does not appear in the Student information table does not exist in the school number.
• Changing the values in the primary table causes the records in the related tables to be orphaned. If the student's information sheet is changed, the student's academic number should be changed as well.
• A record is deleted from the primary table, but a related record that matches the record still exists in the related table. If you delete the student information from the Student information sheet, the student's school number cannot appear in the student's score table.

Referential integrity is achieved through a reference relationship between the primary key and the foreign key.

4. Custom integrity constraints (CHECK constraints)

User-defined integrity is used to define specific rules. For example, when inserting a user record into a user information table, the ID number is required to check for the presence of the user in another database, and if the user's reputation is satisfied, and so on. If you do not meet the requirements, you will not be able to insert, this time you need to use the database rules, stored procedures and other methods to constrain.

Primary key

Has a column that is used to uniquely identify each row in the table to enforce the entity integrity of the table, which is defined as the primary key for the table.

A table can have only one primary key, and the primary key column does not allow null values (null), although there are no primary keys allowed in some tables, it is generally recommended to set a column as the primary key for the table.

Tip: If two or more columns are combined to uniquely identify each row in the table, the primary key is also called a composite primary key .
FOREIGN key

External inspection is relative to the primary key, that is, " from the table " in the column corresponding to the " Main Table ", in the table is called a foreign key or reference key, its value requirements and the primary table of the primary key or unique key corresponds to, Foreign keys are used to enforce referential integrity. A table can have more than one foreign key.

Creating a database table is actually the process of implementing the different constraints that implement the integrity rules.

SQL Server data Types

	Classification	Data type	Description
Binary data types	Data that is used to store non-characters and text	Binary	Fixed-length binary data
		Varbinary	Variable-length binary data
		Image	can be used to store images
Text data type	Character data includes any combination of letters, symbols, or numeric characters	Char	Fixed-length non- Unicode character data with a maximum length of 8000 characters
		Varchar	Variable-length non- Unicode character data
		Nchar	Fixed-length Unicode character data
		Nvarchar	Variable-length Unicode character data
		Text	Storing long text information
		Ntext	Store long text with variable length
Date and time data types	Used to store date and time	Datetime	From 1753 year 1 months 1 days to 9999 years , 1% seconds or 3.33 milliseconds accuracy
Numeric data types	This data contains only numbers, including positive, negative, and fractional	Int,smallint,tinyint,bigint	Integer
		Float,real	Floating point number
Currency data type	The Currency data type is used for decimal currency values and is accurate to 4 digits after the decimal point	Money
Bit Data Type	The data that indicates Yes / No, there are only two options, such as marriage, whether to order, etc. In SQL Server with 1 and 0 , General 1 means yes,0 indicates no	Bit	Storing Boolean data types

Unicode is a character encoding used on a computer that sets a uniform and unique binary encoding for each character in each language to meet the requirements for cross-language, cross-platform text conversion, and processing.

When creating a table, select the character data type, and you also need to enter the length information, such as varchar(a) is a length of character data, where the length can be changed. Selecting a numeric data type does not require the input length information, so that you are fixed for length.

Identity column

In many cases, it is difficult to find information that is not duplicated as the primary key for a column in a stored message. Take the student information, with the name as the primary key, a class will not be allowed to have the same name of the students, if a table to store student information throughout the school, then the whole school is not allowed to duplicate the same, this obviously brings trouble.

SQL Server provides an " identity column "that identifies the column itself as having no specific meaning and does not reflect the student's information, such as address, gender, just to distinguish different students.

Identity columns are implemented in the following ways:

• If the data for a column is of a numeric type (such as an integer), you can define the column as an identity column.
• Once defined as an identity column, you also need to specify the identity seed and identity increment , respectively, and thedefault value is 1.
• After defining the columns of the table, each time the data is entered at a later time, the column automatically increases the value as the data row increases, and does not repeat, the first number is the " identity seed " value, each time following the " Identity increment " increase the value.

The identity column is also typically defined as the primary key, which is usually referred to as an automatic Increment of the number that identifies the column.

The data in the identity column is automatically generated, you cannot enter data on that column, and subsequent chapters will learn to Insert data using SQL statements, and also do not allow values to be specified for the identity column.

Establish CHECK constraints

Check constraints are also called check constraints, which define acceptable data values or formats in a column. Where% represents any number of characters.

s1/c# Language and database technology fundamentals/07-using tables to organize data