Chapter 2 SQLServer database table management

Source: Internet
Author: User
The database itself cannot directly store data, and data is stored through tables in the database. Before creating and managing a database table, you must understand the following concepts. 2.1.1 data integrity requires that the data in the database be accurate. accuracy is achieved through the design and constraints of the database table.

The database itself cannot directly store data, and data is stored through tables in the database. Before creating and managing a database table, you must understand the following concepts. 2.1.1 data integrity requires that the data in the database be accurate. accuracy is achieved through the design and constraints of the database table.

The database itself cannot directly store data, and data is stored through tables in the database.

2.1 basic concepts related to tables

Before creating and managing database tables, you need to understand the following concepts.

2.1.1 Data Integrity

Data integrity requires that the data in the database be accurate. The accuracy is achieved through the design and constraints of the database table. For example, if any student information is allowed in the table storing student information, the same table may contain the information of one student; in addition, if the age information stored in the table is not restricted, the age of the student may be negative, and such data cannot be complete.

In order to achieve integrity, the database needs to do the following two work.

Check whether each row of data meets the requirements.

Check whether each column of data meets the requirements.

To meet the preceding requirements, SQL Server provides the following four types of constraints (Constraint ).

1. entity integrity constraints

Entity integrity requires that each row of data in the table reflect different entities and cannot have the same data row.

The entity integrity of a table can be achieved through indexes, unique constraints, progressive constraints, or column attributes.

2. Domain integrity constraints

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

Table domain integrity can be achieved by limiting data types, checking constraints, input formats, foreign key constraints, default values, and non-empty constraints.

3. integrity constraints of reference

When you enter or delete data rows, the reference integrity constraints are used to maintain the defined relationships between tables.

For example, when managing student information, a table is used to store the student information, and a table is used to store the details of the exam scores. A column of values in the exam scores table is the number in the student information table, used to indicate the student's exam.

  

We can see that the two tables have a "relationship". The student information table is a "master table" and the student renewal table is a "sub table" (sometimes called a "related table ").

When you forcibly reference integrity, SQL Server prohibits you from performing the following operations.

When there are no associated records in the primary table, add the records to the relevant table. That is to say, the student renewal table cannot contain a student ID that does not exist in the student information table.

Change the values in the primary table and isolate the records in the relevant table. If the student ID in the student information table changes, the student ID in the student renewal table should also change.

Delete a record from the primary table, but there are still related records that match the record. If you delete a student from the student information table, the student ID cannot appear in the student renewal table.

Integrity of reference is achieved through the quotation mark relationship between the foreign key and the foreign key gradually.

4. Custom integrity constraints

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.