SQL Server (table creation and constraint)

Posted by Nommy on Mon, 29 Nov 2021 14:16:58 +0100

In the process of using the database, the most exposed is the table in the database, which stores all the data in the database. Therefore, the table is the basic information storage structure of SQL Server database system and the most important part of the database. Managing the table naturally manages the database.

1. Data sheet overview

         Data table is one of the most important parts of database. Database is just a framework, data table is its essence, and all data is stored in data table. According to the classification of information, a database may contain several data tables.

        In the process of creating a data table, because you want to define the fields in the table, you need to select the data type for the fields. The following describes the basic types commonly used in SQL Server 2008 R2.

2.SQL basic data type

classification

Remarks and instructions

data type

explain

Binary data type

Stores data that is not child characters and text

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

Varchar

Variable length non Unicode data

Nchar

Fixed length Unicode data

Nvarchar

Variable length Unicode data

Text

Store long text information

Ntext

Store long text of variable length

Date and time

Enter the date and time in single quotation marks

Datetime

Date and time

digital data

The data contains only numbers, including positive numbers, negative numbers, and fractions

int

smallint

integer

float

real

number

Currency data type

Used for decimal currency values

Money

Bit data type

Data indicating yes / no

Bit

Store Boolean data types

3. Data table creation

There are two ways to create data tables through sql structured query language: (1) object resource manager (2) script

3.1 creating tables using object Explorer

  3.2 creating tables by script

create table tb_ Table name

(

        Property name data type [(length)],

         Property name data type [(length)],

         Property name data type [(length)]

)

/*Create student information table*/
create table tb_student
(
    sid int,
    sname varchar(20),
    ssex char(2),
    sage int
)

4.SQL constraints

constraint is a method provided by Microsoft SQL Server to automatically maintain database integrity. It defines the constraints that can input data in a table or a single column of a table.

There are six kinds of constraints in SQL Server: Primary Key constraint, Foreign Key constraint, Unique constraint, Check constraint, Default Constraint and not null constraint.

4.1 primary key constraint

The primary key constraint uniquely identifies each record in the database table. The primary key must contain unique values. Primary key columns cannot contain NULL values. Each table should have a primary key, and each table can only have one primary key.

 

create table tb_Table name
(
    Attribute data type[(length)] primary key
)

4.2 foreign key references

A foreign key is one or more columns used to establish or strengthen a link between two table data. By adding one or more columns of the primary key value in a table to another table, you can create a connection between two tables, and this column becomes the foreign key of the second table.
The purpose of foreign key constraint is to control the data stored in the appearance and the modification of the data in the primary key table

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)]
)

create table tb_Table name 2
(
    Attribute 1 data type[(length)] foreign key references tb_Table name 1(attribute),
    Attribute 2 data type[(length)]
)

4.3 unique ness constraint

Unique constraints ensure that a column of data in a table does not have the same value. Similar to the primary key constraint, the unique constraint also enforces uniqueness, but the unique constraint is used for one or more columns of non primary key columns, and a table can define multiple unique constraints.

 

 

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] unique
)

4.4 check constraints

Check constraints are used to limit the range of values in a column. If a check constraint is defined for a single field (column), only specific values are allowed for that column. If you define a check constraint on a table, the constraint limits values in specific columns.

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] check ( Attribute 2 = '' or Attribute 2 = '')
)

4.5 default constraint

If the default value constraint is defined in the table, when inserting a new data row, if the row does not specify data, the system assigns the default value to the column. If we do not set the default value, the system defaults to null.

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] default 'Default value'
)

4.6 non NULL constraint not null

Non NULL constraint means that the value of the field cannot be null. For fields with non null constraints, if the user does not specify a value when adding data, the database system will report an error.

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] not null
)

5. Create a classic case table

--Student information sheet
create table tb_student
(
    sid int primary key,--Student number
    sname varchar(50) not null,--full name
    ssex char(2) check(ssex = 'male' or ssex = 'female'),--Gender
    sage int not null,
    saddress varchar(100) default 'Changsha City, Hunan Province',--address
    sidentityCard varchar(20) unique,--ID card No.
)
--Transcript
create table tb_score
(
    sid int foreign key references tb_student(sid),--Foreign key
    degree int
)

6. Expansion

--Modify the structure of the table

--Modify field type
	alter table Table name
	alter column Field name type constraint;

--Add or delete columns
	alter table Table name
	add Field name type constraint
	
	alter table Table name
	drop column Field name


--Add or remove constraints
	Not empty:
	alter table Table name
	alter column Field name type constraint;
	
--other
	alter table Table name
	add constraint Constraint name(Field name)

Topics: Database SQL Server SQL