SQL Server2012 Creating constraint plots

There are five types of constraints in SQL Server: Primary key constraint, Foreign key constraint, UNIQUE constraint, default constraint, and check constraint

1 . Primary Key constraint

There is often a combination of one or more columns in a table whose values uniquely identify each row in the table.

Such a column or columns becomes the primary key (PrimaryKey) of the table. A table can have only one primary key, and a column in a PRIMARY KEY constraint cannot be a null value. Only the primary key column can be created as a foreign key for other tables.

To create a primary key constraint, you can right-click the table and select design.

Select the column where you want to create the primary key, and then click the Small key above.

You can also right-click the column where you want to create the primary key, and then press the small key.

2 . Foreign Key constraint

FOREIGN KEY constraints are used to strengthen the connection between two tables (the primary table and the table) between one column or more columns of data. The order in which foreign key constraints are created is to first define the primary key of the primary table and then define the foreign key from the table. In other words, only the primary key of the main table can be used as a foreign key from the table, and the constrained column from the table may not be the primary key, and the primary table restricts the actions that are updated and inserted from the table.

Right-click the column where you want to set the foreign key (at which point the table appears as an external key from the table), and select the relationship.

Next click Add --table and column specifications.

In the primary key table, select the primary key column for the primary table and the primary table.

Save it when you're finished setting it up.

3 . Unique constraint

A unique constraint ensures that a column of data in a table does not have the same value. Like a primary KEY constraint, a unique constraint also enforces uniqueness, but a unique constraint is used for a combination of one or more columns of a non-primary key, and a table can define multiple unique constraints.

Right-click the column you want to set to select the index / key.

And then click the Add button.

Select the columns you want to set, either a column or a combination of multiple columns.

Close and save the settings.

4 . Default constraint

If a default value constraint is defined in a table, when a user inserts a new data row, if the row does not have the specified data, the system assigns the default value to the column, and the system defaults to NULL if we do not set the default value .

For the Student information table, for example, in Table Designer, fill in the default value for the sex sex column male.

5 . Check constraint

a Check constraint uses a logical expression to determine the validity of the data and to limit the range of values that are entered into one or more columns. When you update data in a column, the content that you want to enter must meet the criteria for the Check constraint, or it will not be entered correctly.

In the case of sex in the Student information table , we want to limit the value of the sex column to only male or female.

SQL Server2012 Creating constraint plots