Database How SQL Server 2008 views and creates constraints

There are five types of constraints in SQL Server, namely the PRIMARY key constraint, the FOREIGN key constraint, the unique constraint, the default constraint, and the check constraint. View or create constraints that you want to use to Microsoft SQL Server Managment Studio.

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 of the table (Primary key). A table can have only one primary key, and a column in a PRIMARY KEY constraint cannot be a null value.

View PRIMARY KEY constraints can be expanded in the Object Explorer databases–> select the database you want to view (in my case, testdatabase) –> tables–> The table you want to view (in my case, company), Columns

As shown, Primary Key has a small golden key. CompanyID is the primary key for the company table.

To create a primary key constraint, you can right-click on the table and select design to open the Table Designer

Select column and click on the Golden key above to create the primary key.

You can also right-click the column and select Set Primary key.

2. FOREIGN key Constraint

A foreign key (Foreign key) is used to establish and strengthen a connection between two tables (the primary table and a column or columns of data from a table). The order in which constraints are created is to define the primary key for the primary table, and then to define a foreign key constraint from the table.

View FOREIGN KEY constraints, expand columns, you can see the small gray key is foreign key, expand keys, you can see the foreign Key constraint name is Fk_contact_company.

In the Table Designer, you can also click the Relationships button above, so you can see all the foreign KEY constraints

The above example can see the CompanyID of the Contact table as the foreign key, the company table of the CompanyID primary key.

Here's a demonstration of how to create the foreign key constraint.

Also after clicking the Relationships button, select Add in the popup dialog box.

Then click the button in the red circle below:

Set the primary table, primary key, and from table, foreign key as in

Then click OK and don't forget to save your design.

3. Unique constraints

Unique constraints are used to ensure that two rows of data in a table do not have the same column values in a non-primary key. Like the primary KEY constraint, a unique constraint enforces uniqueness, but a unique constraint is used for one or more column combinations of non-primary keys, and one table can define multiple unique constraints, and a unique constraint can be used to define a multiple-column combination.

Also take company table as an example, suppose we want to constrain company name to unique, click Manage Indexes and Keys

Then click Add to add a unique constraint

Select Column as CompanyName, is unique yes.

Close and save your design so that a unique constraint is created.

4. Default Constraints

If a column in a table defines a default constraint, the default value is assigned to the column if the user inserts a new row of data, and the default value can also be null (NULL) if the column does not have the specified data.

For example in the Contact table, in Table Designer, fill in the attribute default value (' M ') for the Gender (sex) column.

5. Check constraints

A check constraint is used to restrict the range of values entered into one or more columns, and to determine the validity of the data through a logical expression. The input for a column must meet the criteria of the check constraint, otherwise the data cannot be entered correctly.

Also take the Contact table as an example, we want to limit the value of the sex column to only ´m´ or ´f´. In the Table Designer, click Manage Check Constraints

Tap Add to add a new constraint

Click the Red circle button to fill in the expression. The expression in our example is sex= ' M ' OR sex= ' F '

Close and save the design. Inserts a row of data into the Contact table and inserts an error if the value of the sex column is not ´m´ or ´f´.

Database How SQL Server 2008 views and creates constraints