SQL Server Create constraint diagram (unique primary key) _mssql

There are five types of constraints in SQL Server, Primary key constraints, Foreign key constraints, UNIQUE constraints, default constraints, and check constraints, which today use SQL Server2008 to demonstrate the way in which these constraints are created and used.

What is a primary key?

In a database, often not just a table, these tables are not independent of each other. Different tables need to establish a relationship in order to communicate their data to each other. In the process of communication, it is necessary to have a field in the table as a flag, different records corresponding to the value of the field can not be the same, can not be blank. Each record can be distinguished by different values in this field. Just as we distinguish different people, everyone has a name, but it can not be a primary key, because the name is very easy to repeat, and the identity card number is different for everyone, so can be based on it to distinguish different people. A field in a database table as a primary key must be the same as a person's identity card number, so that different records can be determined based on the value of the primary key.

If you specify a PRIMARY key constraint for a table, the SQL Server 2005 database engine enforces the uniqueness of the data by creating a unique index from the primary key column. This index can also be used for quick access to data when a primary key is used in a query. Therefore, the selected primary key must adhere to the rules for creating a unique index.

When you create a primary key, the database engine automatically creates a unique index to enforce the uniqueness requirements for the PRIMARY key constraint. If a clustered index does not exist in the table or does not explicitly specify a nonclustered index, a unique clustered index is created to enforce the PRIMARY KEY constraint.

Uniqueness Constraint
1 The column that contains the uniqueness constraint allows null values, but the column that contains the PRIMARY KEY constraint does not allow null values.
(2) A Uniqueness constraint can be placed on one or more columns, and the combination of these columns or columns must have only one. However, the column that contains the uniqueness constraint is not the primary key column of the table.
(3) A Uniqueness constraint forces a unique index to be created on the specified column. By default, nonclustered indexes of uniqueness are created, but you can also specify that the index being created is a clustered index.

Primary key:
1) is used to identify a row and relate to it.
2) is not possible (or difficult) to update.
3 should not allow null (NULL).

Unique Domain/field:
1) used as an optional means of accessing a row.
2) As long as the only one can be updated.
3) can be empty (NULLs).

Note that the difference between unique and primary keys is to create a unique index, a table containing only one PRIMARY KEY constraint column, but it is possible to have many unique constraints in other columns.

1. Primary key Constraint

There is often a combination of one or more columns in a table, and its value uniquely identifies each row in the table.

One or more of these 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 primary key columns can be created as foreign keys in other tables.

Create a PRIMARY KEY constraint you can right-click the table and choose the design.

Select the column for which 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 click the Small key.

2. Foreign key Constraint

A FOREIGN KEY constraint is used to strengthen the connection between one or more columns of data in two tables (primary and from tables). 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. That is, only the primary key of the primary table can be used as a foreign key from the table, and the constrained columns from the table may not be primary keys, and the primary table restricts the actions that are updated and inserted from the table.

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

Next click Add--> table and column specification.

Select the primary key column of the primary table and primary table in the primary key table.

Save when you are finished setting.

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 enforces uniqueness, but a unique constraint is used for a column or multiple-column combination of a Non-key key, and a table can define multiple unique constraints.

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

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, and if the user inserts a new row of data, the system assigns the default value to the column, and if we do not set the default value, the system defaults to NULL.

Take the Student information table as an example, in Table Designer, fill out the default value for the Gender 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 entered into one or more columns. When you update data in a column, the content you want to enter must meet the condition of the check constraint, or it will not be entered correctly.

Take sex in the Student information table as an example, we want to limit the value of the sex column to only men or women.

Close and save the design.

To this, five kinds of constraints in the database are reviewed again, the constraints ensure the integrity of the data in the database, but only constraints are not enough.