SQL Server Database tutorial Four, data type constraints

SQL constraints are used to restrict the data types that are joined to a table.

Note: You can pass the constraint (through the CREATE TABLE statement) When you create the table, or you can do so after the table is created (ALTER table statement).

The main constraints have the following points:

• Not NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

1>not NULL

SQL NOT NULL constraint is used to force columns to not accept null values

For example, in creating a person table, setting the data for the column id_p does not accept null values, which means that if you do not add a value for the field, you will not be able to insert and update records for that column, as follows:

 create  table   Persons (id_p  int  not  null  ,lastname  varchar  (not  null  ,firstname  varchar  (255  ), Address  varchar  ( 255   varchar  (255   

2>unique

A SQL unique constraint that constrains each record in the database table that uniquely identifies it.

For example, in the persons table, the data for id_p column is specified as a non-repeating int data with the following code:

CREATE TABLEPersons (id_pint  not NULL UNIQUE, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255))

Then it means that the data in this column cannot be null and is uniquely identified by Id_p.

3>primary KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Attention:

The primary key must contain a unique value.

Primary key columns cannot contain NULL values.

Each table should have a primary key, and each table can have only one primary key.

For example, in the persons table, set id_p as the primary key, then id_p will be the unique identifier for each record, with the following code:

CREATE TABLEPersons (id_pint  not NULL PRIMARY KEY, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255))

4>foreign KEY

The FOREIGN key in one table points to the PRIMARY key in the other table.  

FOREIGN key is also called a foreign key, let's use an example to illustrate the relationship between the primary key and the foreign key.

Take a look at two tables:

Attention:

The id_p column in Orders points to the id_p column in the Persons table.

The "id_p" column in the "Persons" table is the PRIMARY KEY in the Persons table.

The "id_p" column in the Orders table is the FOREIGN KEY in the Orders table.

The FOREIGN KEY constraint is used to prevent actions that disrupt the connection between tables.

The FOREIGN key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to.

Example code:

CREATE TABLE  int notNULLPRIMARYKEY int. not NULL  intFOREIGNKEYREFERENCES  Persons (id_p))

SQL Server Database tutorial Four, data type constraints