Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to create constraints in SQL Server. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

What is the primary key?

In a database, there is often more than one table, and these tables are not independent of each other. Different tables need to establish a relationship in order to communicate their data with each other. In this communication process, it is necessary to have a field in the table as a sign, and the corresponding fields of different records can not be the same, nor can they be blank. Each record can be distinguished by different values in this field. Just like we distinguish different people, everyone has a name, but it can not be used as a primary key, because the name is easy to repeat, and the ID number is different for everyone, so we can distinguish different people according to it. The field in the table of the database as the primary key must be the same as the human ID number, and the value of each record must be different, so that different records can be determined according to the value of the primary key.

If a PRIMARY KEY constraint is specified for the table, the SQL Server 2005 Database engine enforces the uniqueness of the data by creating a unique index for 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 follow 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 of the PRIMARY KEY constraint. If a clustered index does not exist in the table or a nonclustered index is not explicitly specified, a unique clustered index is created to enforce the PRIMARY KEY constraint.

Uniqueness constraint 1) the column in which the uniqueness constraint is located allows null values, but the column in which the primary key constraint is located does not allow null values. (2) the uniqueness constraint can be placed on one or more columns, and the combination of these columns or columns must be unique. However, the column where the uniqueness constraint resides 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, but you can also specify that the index created is a clustered index.

Primary key: 1) used to identify a row and associated with it. 2) it is impossible (or difficult) to update. 3) null (NULL) should not be allowed.

Unique field / field: 1) used as an optional means of accessing a row. 2) as long as it is unique, it can be updated. 3) it can be empty (NULLs).

Note the difference between unique and primary keys, which create a unique index, and a table contains only one primary key constraint column, but it is possible that it contains many unique constraints in other columns.

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 become the primary key (PrimaryKey) of the table. A table can have only one primary key, and the columns 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, you can right-click the table and select 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

Foreign key constraints are used to strengthen the join between one or more columns of data in two tables (master and slave tables). The order in which foreign key constraints are created is to define the primary key of the primary table and then the foreign key of the slave table. In other words, only the primary key of the master table can be used as a foreign key in the slave table, the columns in the constrained slave table may not be the primary key, and the master table restricts the operation of updating and inserting from the slave table.

Right-click the column where you want to set the foreign key (at this time the table appears in the foreign key as a slave table) and select the relationship.

Next, click add-> Table and column specifications.

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

Save it after setting it up.

3. Unique constraint

Unique constraints ensure that a column of data in a table does not have the same value. Like primary key constraints, unique constraints also enforce uniqueness, but unique constraints are used for one or more columns that are not primary keys, and a table can define multiple unique constraints.

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

Then click the add button.

Select the column you want to set, which can be a combination of one column or multiple columns.

Close and save the settings.

4. Default constraint

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

Take the student information table as an example, in the table designer, fill in the default male value for the gender sex column.

5. Check constraint

Check constraints determine the validity of data through logical expressions and are used to limit the range of values entered in one or more columns. When updating data in a column, what you want to enter must meet the conditions of the Check constraint, otherwise it will not be entered correctly.

Taking the sex in the student information table as an example, we want to limit the value of the sex column to 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 constraints alone are far from enough.

After reading the above, do you have any further understanding of how to create constraints in SQL Server? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.