Learning SQL Server (2): data table constraints

Time:2020-12-23

–Data table constraint: by making some rules, the data stored in the database is standardized, correct and complete.

–Non NULL constraint the field cannot be empty keyword: not null

–The value of the unique constraint field cannot be repeated in this table and can be null, but only once. Keyword unique

–The default constraint gives the field a default value. If it is empty, it will be set as the default value. If it is not empty, it will be defaulted according to your input

–The check constraint gives the field a rule, and the input data must conform to this rule. Keyword check

–The primary key constrains the field to be the unique identifying column of this table. This is usually the ID column. The effect of the primary key constraint is = = unique constraint + non NULL constraint. Only one primary key can be set per table.
–Keyword primary key

–Foreign key constraint, which regulates the relationship between the corresponding fields of two tables. The foreign key constraint implements referential integrity (referential consistency) of two table data
–Keyword foreign key references main table name (corresponding field name)
–Foreign key constraint the column data must come from the corresponding field of the main table [most of the corresponding field names are primary keys]. The foreign key can be empty and repeatable

–Identity (start value, self increment)

create table StuScore
(
StudentId int not null unique,

Name char(20),
Score decimal(4,1),
Course nchar(40),
TestTime datetime,
Home nvarchar (20) default ‘Shaanxi’ check (home =’shaanxi ‘or home =’henan’), — only Shaanxi and Henan (default is the default value)
Age smallint default 18 check (age > = 0 and age < = 20), — age > = 0 and < = 20
–String type single citation
)

create table stu
(
Id int primary key,
StuId int foreign key references StuScore(StuId),
)

 

The author is still learning. If you find any mistakes, please leave a message in the comment area. If you think the article is OK, please recommend it. Thank you!!