SQL Server creation constraint diagram (unique primary key), SQL Server

SQL Server creation constraint diagram (unique primary key), SQL Server

SQLServer has five constraints: Primary Key constraint, Foreign Key constraint, Unique constraint, Default constraint, and Check constraint. Today, SQL Server2008 is used to demonstrate how to create and use these constraints.

What is a primary key?

In a database, it is often not just a table, and these tables are not mutually independent. Different tables need to establish a relationship to communicate their data. In this communication process, a field in the table must be used as a flag. The values of fields corresponding to different records cannot be the same or blank. Different values in this field can be used to differentiate each record. Just as we have different people, each person has a name, but it cannot be used as a primary key, because the names are easily duplicated, and the ID card number is different for everyone, so we can differentiate people based on it. The fields used as the primary key in the database table must be the same as the ID card number of a person. The values of each record must be different so that different records can be determined based on the value of the primary key.

If the primary key constraint is specified for the table, the SQL Server 2005 database engine creates a unique index for the PRIMARY KEY column to force data uniqueness. When a primary key is used in a query, this index can also be used to quickly access data. Therefore, the selected primary key must comply with the rules for creating a unique index.

When a primary key is created, the database engine automatically creates a unique index to enforce the uniqueness requirements of the primary key constraint. If no clustered index exists in the table or the non-clustered index is not explicitly specified, a unique clustered index is created to enforce the primary key constraint.

Uniqueness Constraint
1) The column where the uniqueness constraint is located allows null values, but the column where the primary key constraint is located does not allow null values.
(2) You can place the uniqueness constraint on one or more columns. The combination of these columns or columns must be unique. However, the column where the uniqueness constraint is located is not the primary key column of the table.
(3) The uniqueness constraint forces the creation of a unique index on the specified column. By default, a unique non-clustered index is created. However, you can also specify that the created index is a clustered index.

Primary key:
1) identifies a row and is related to it.
2) It is impossible (or difficult) to update.
3) NULL is not allowed ).

Unique domain/field:
1) used as an optional means to access a row.
2) It can be updated as long as it is unique.
3) it can be null (NULLs ).

Note the difference between unique and primary keys. They all create a unique index. A table contains only one primary key constraint column. However, it may contain many unique constraints in other columns.

1. Primary Key constraint

A table usually contains a combination of columns or multiple columns. Its values can uniquely identify each row in the table.

Such one or more columns become the primary key of the table ). A table can have only one primary key, and the column in the primary key constraint cannot be null. Only primary key columns can be created as foreign keys of other tables.

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

 

Select the column for which the primary key is to be created, and click the small key above.

 

You can also right-click the column for which you want to create a primary key and click the small key.

 

2. Foreign Key constraint

The foreign key constraint is used to enhance the connection between one or more columns of two tables (the primary table and the slave table. The order in which foreign key constraints are created is to first define the primary key of the master table, and then define the foreign key of the slave table. That is to say, only the primary key of the primary table can be used as the foreign key in the slave table. The column in the restricted slave table can be not the primary key. The primary table limits the update and insert operations on the slave table.

Right-click the column for which you want to set the foreign key (the table appears as the foreign key in the table) and select the link.

Next, click Add --> table and column standards.

In the primary key table, select the primary key columns of the primary table and the primary table.

 

Save the settings.

3. Unique constraints

The unique constraint ensures that a column of data in the table does not have the same value. Similar to the primary key constraint, the unique constraint also enforces uniqueness. However, the unique constraint is used for the combination of one or more columns without the primary key. A table can define multiple unique constraints.

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

Click Add.

Select the column to be set. It can be a combination of one or multiple columns.

Close and save the settings.

4. Default Constraints

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

Take the student information table as an example. In the Table Designer, enter the default male value for the gender sex column.

5. Check Constraints

Check constraints Use logical expressions to determine the validity of data. They are used to limit the range of values of one or more columns. When updating data in a column, the content to be entered must meet the Check constraints. Otherwise, the data cannot be entered correctly.

Take sex in the student information table as an example. We want to limit that the sex column value can only be male or female.

Close and save the design.

As a result, the five constraints in the database are reviewed again. Constraints ensure the integrity of the data in the database, but only constraints are far from enough.