SQL Server 2008 graphic tutorial on viewing and creating constraints

There are several constraints:

Not null: the content used to control fields must NOT be NULL ).

UNIQUE: The control field content cannot be repeated. A table can have multiple Unique constraints.

Primary key: it is also used to control the content of fields that cannot be repeated, but it can only appear in one table.

Foreign key: the foreign key constraint is used to prevent the action of damaging the connection between tables. The foreign key constraint can also prevent illegal data insertion into the foreign key column, because it must be one of the values in the table to which it points.

CHECK: used to control the value range of a field.

DEFAULT: set the DEFAULT value of the new record.

 

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.

View the primary key constraint. In object explorer, expand Databases> select the database you want to view (in my example, testdatabase) -> Tables-> the table you want to view (in my example, company)-> Columns

As shown in the preceding figure, the Primary Key has a small golden Key. Companyid is the primary key of the company table.

 

To create a primary key constraint, right-click the table and select Design to open the table designer.

Select column and click the golden Key above to create the Primary Key.

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

 

 

2. foreign key constraint

The Foreign Key (Foreign Key) is used to establish and enhance the connection between one or more columns of two tables (master table and slave table. The order in which constraints are created is to first define the primary key of the master table, and then define the foreign key constraints for the slave table.

View the foreign key constraint and expand Columns. The gray Key is Foreign Key. Expand Keys. The Foreign KEY constraint is named FK_contact_company.

In the table designer, you can also click the Relationships button above to view all the Foreign Key constraints.

In the preceding example, the companyid of the contact table is a foreign key, and the companyid of the company table is a primary key.

 

The following describes how to create the Foreign Key constraint.

After clicking the Relationships button, select Add in the displayed dialog box.

Click the button in the red circle below:

Set the master table, primary key, slave table, and foreign key as shown in the following figure.

Click OK. Do not forget to save your design.

 

 

3. UNIQUE constraints

The UNIQUE constraint is used to ensure that two data rows in the table do not have the same column value in the non-primary key. Similar to the primary key constraint, the UNIQUE constraint is also forced to be UNIQUE. However, the UNIQUE constraint is used to combine one or more columns of a non-primary key. A table can define multiple UNIQUE constraints, in addition, the UNIQUE constraint can be used to define multi-column combinations.

 

Take company table as an example. If we want to restrict the company name to a unique one, click Manage Indexes and Keys.

Click Add to Add the Unique constraint.

Set column to companyname and Is Unique to Yes.

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

 

 

4. DEFAULT constraints

If a column in the table defines the DEFAULT constraint, when you insert a new data row, if the column does not specify data, the system assigns the DEFAULT value to the column, of course, the default value can also be NULL ).

 

Take the contact table as an example. In the table designer, enter the default attribute value ('M') for the sex column ').

 

 

5. CHECK constraints

The CHECK constraint is used to restrict the range of values of one or more input columns and determine the validity of data through logical expressions. The input content of a column must meet the CHECK constraints. Otherwise, data cannot be normally input.

 

Take the contact table as an example. We want to restrict the value of the sex column to only & acute; M & acute; or & acute; F & acute ;. In the table designer, click Manage Check Constraints.

Click Add to Add a new constraint.

Click the button in the red circle and enter the expression. The expression used in this example is SEX = 'M' or sex = 'F'

Close and save the design. Insert a row of data into the contact table. If the sex column value is not & acute; M & acute; or & acute; F & acute;, an error is returned when inserting

Delete:

The code is as follows:	Copy code
Delete the Unique constraint syntax in SQL Server, Oracle, and MS Access: drop constraint UniqueName; Delete Unique constraint syntax in My SQL: drop index UniqueName;

Modify:

The code is as follows:	Copy code
Alter table EPlatform Add constraint Unique_EPlatform Unique ([UserId], [Platform]);