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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.