SQL base organizes data with tables

Time:2021-1-18

What is data integrity?

Data integrity = Data accuracy+ data reliability

 

How to classify data integrity?

1. Entity integrity (line)

2. Domain integrity (column)

3. Referential integrity (key relationship between tables)

4. Custom integrity

 

How to ensure the integrity of data?

It is necessary to implement integrity constraints on the database when creating tables

Entity integrity constraints

(line)

Primary key constraint
Identity column

Unique constraint

Domain integrity constraints

(column)

data type

Non NULL constraint (not null)
Default value
check constraints
Foreign key constraint

Referential integrity constraints

(two tables)

Foreign key constraint

Custom integrity constraints

Rules, stored procedures, triggers

 

When you want to create a table to save data, you must determine the type of data to saveWhat are the data types in T-SQL?

number

Integer int

Float real numric decimal

character string

Fixed length string char

Variable length string varchar

Unicode encoding fixed length string nchar

Unicode encoding variable length string nvarchar

Big text

Unicode encoding large text ntext

date

datetime

True or false

bit

other

Money

Data type considerations:

The time date type and string type are enclosed in a pair of single quotation marksFor example: ‘2010-1-22’

 

Implementation of main constraints

1. Primary key

Primary key column requirement: no duplicate and no null value

Primary key selection method:

If there is a column in the u table that can be used to uniquely identify the rows in the table, the column is directly selected as the primary key column, such as the student number column

If there are no columns in the u table that can be used to uniquely identify the rows in the table, you can identify them by creating a combined primary key

 

2. Identification column

Identification column requirement: the data type of the column can only beInt type, and cannot have null value

How to set identity column:

selectionInt type column. In the attribute window of this column, expand the identification range and set it as identification. At the same time, set the identification increment (increment step) and Identification Seed (initial identification value). The default value is 1

Characteristics of identification column: the identification column will be automatically added each time according to the identification increment and identification seed

 

If the type of the primary key column isInt type, the primary key column is usually set as the identification column at the same time

 

3. Primary foreign key relationship: the relationship between two tables

The table to be included and referenced is the primary key table

A foreign key table must have a column (foreign key column), which refers to the primary key column of the primary key table

You can use the database diagram to drag and drop to create foreign key constraints directly

Recommended Today

Go carbon version 1.2.0 has been released to improve and optimize the multi scenario support for orm

Carbon is a lightweight, semantic and developer friendly golang time processing library, which supports chain call, lunar calendar, Gorm, xorm and other mainstream orm If you think it’s good, please give it to a star github:github.com/golang-module/carbon gitee:gitee.com/go-package/carbon Installation and use //Using GitHub Library go get -u github.com/golang-module/carbon import ( “github.com/golang-module/carbon”) //Using gitee Library go get […]