SQL Server-Constraints

I. Classification of constraints

In SQL Server, there are 3 different kinds of constraints.

1. Entity constraints

Entity constraints are about rows, such as when a row appears with values that are not allowed to appear on other rows, such as a primary key.

2. Domain constraints

Domain constraints are about columns, and for all rows, a column has those constraints, such as a check constraint.

3. Referential integrity constraints

If a column's value must match the value of another column, it means that a referential integrity constraint, such as a foreign key, is required.

Second, the constraint named

Before learning constraints, first understand what to look for when naming constraints.

SQL Server automatically creates names when we do not provide names, but names that are automatically created by the system are not particularly useful.

For example, the system-generated primary key name might be this: pk_employees_145c0a3f.

The PK represents the primary key (primary key), employees is represented in the Employees table, and the remaining "145c0a3f" part is a randomly generated value to ensure uniqueness. This value is only available through script creation, and if you create a table from Managerment Studio, it is pk_employees directly.

For the system automatically generated check constraint names such as: ck_customers_22aa2996. CK represents this is a check constraint, customers represents the Customers table, followed by a 22aa2996 or a random number. If there are multiple check constraints in a table, the naming might look like this:

ck_customers_22aa2996

ck_customers_25869641

ck_customers_267aba7a

If you need to modify one of these constraints, it's hard to tell which of these constraints are.

So, to be able to see what this constraint is for at a glance, we should use a simple and straightforward phrase to name it.

For example, to ensure that a column phone number is properly formatted, we can use a phrase named Ck_customers_phoneno to name it.

In short, the naming to do the following points:

1. Consistency

2. Easy to understand

3, to meet the above two conditions under the circumstances of the simplified name.

Third, key constraints 1, PRIMARY KEY constraint

A primary key is a unique identifier for each row, which can be positioned exactly to a single row, where the primary key column cannot have duplicates in the entire table and must contain a unique value (cannot be null). Because of the importance of the primary key in the relational database, it is the most important of all keys and constraints.

Here's how the primary key is created

　　1. Create a PRIMARY KEY constraint when creating a table.

Create Table Customer (    customerId        intidentitynot     null         PrimaryKey, -- Create a PRIMARY KEY constraint    CustomerName    nvarchar(30 ) not    null);

How, very simple!

　　2. Create a PRIMARY KEY constraint on a table that already exists

Now suppose a table already exists, but there is no primary KEY constraint:

Alter Table person     Add constraint pk_employee_id -- foreign key name    primarykey(personId)- -personId Field name

The ALTER name tells SQL Server the following information:

1, add some content to the table (you can also delete some content in the table)

2. What content is added (a constraint)

3. Naming constraints (allowing direct access to constraints later)

4. Type of constraint (PRIMARY KEY constraint)

5. Which column the constraint applies to.

3. Creation of composite primary keys

If you are really management Studio, create a composite primary key, just hold down the CTRL key, select two columns, then set the primary key is OK, very simple. Here's how to create a composite primary key using T-sql:

ALTER TABLE  with NOCHECK ADD CONSTRAINT [pk_ table name ] PRIMARY KEY nonclustered  [ field name 1][ field Name 2]

In many-to-many connections, there is often a table describing the relationship of the other two tables, with the reader and the book as an example:

ALTER TABLE  ADDCONSTRAINT[pk_readerandbook]PRIMARYKEY  nonclustered  (Readerid, BookId)

2, FOREIGN KEY constraints

Foreign keys both ensure data integrity and can represent relationships between tables. After a foreign key has been added, the record that inserted the reference table must either be matched by a record of the referenced column in the referenced table, or the value of the foreign key column must be set to NULL.

Foreign keys and primary keys are different, and the number of foreign keys in each table does not limit uniqueness. In each table, each has a-~253 foreign key. The only restriction is that a column can reference only one foreign key. A column can be referenced by more than one foreign key.

　　1. Create a foreign key when creating a table

Create Table orders (    orderId        intidentitynot    null        Primary  key,    customerId    intnot                null        Foreign Key references customer (CUSTOMERID) -- constraint type-foreign key-reference table (column name));

2. Add a foreign key to a table that already exists

Assuming the above code removes the addition of the foreign key row, you can write the following code:

Alter Table orders     Add constraint Fk_orders_customerid        -- Add constraint name        foreignkey (CustomerId)    references customer (customerId)    -- FOREIGN KEY constraint, foreign key column name, referenced column name

The constraint you just added takes effect as a previously added constraint, and if a row reference CustomerID does not exist, you are not allowed to add the row to the Orders table.

　　3. Cascade Action

One important difference between foreign keys and other type keys is that the foreign key is bidirectional, that is, not only the value of the restriction child table must exist in the parent table, but also the child rows after each action on the parent table (this avoids orphan). The default behavior of SQL Server is that the "limit" parent row is deleted when a child row exists. However, there are times when any dependent records are automatically deleted, rather than preventing the referenced records from being deleted. Also when you update a record, you may want to rely on records that automatically reference the records that you just updated. In rare cases, you might want to change the reference row to a known state. To do this, you can choose to set the value of the dependent row to null or the default value for that column.

This process of automatic deletion and automatic updating is called cascading. This process, especially the removal process, can go through several layers of relationships (one record relies on another record, and this other record relies on other records). Implementing level linkage in SQL Server requires you to modify the foreign key syntax-just precede the addition with an ON clause. For example:

Alter TableordersAdd constraintFk_orders_customerid--Add constraint name        Foreign Key(customerId)ReferencesCustomer (CUSTOMERID)--FOREIGN KEY constraint, foreign key column name, referenced column name         on UpdateNo action--do not cascade update child tables when modified by default         on Delete     Cascade      --Cascade Delete Dependent rows when deleting

When cascading deletes, if one table cascade another table, and the other table cascade the other table, this kind of federation has been down, unrestricted, this is actually a cascade of danger, it is easy to accidentally delete a large amount of data.

The level linkage is made in addition to the no action,cascade, there are set NULL and set default. The latter two are introduced in SQLServer2005, and if you want to be compatible with SQLServer2000, avoid using these two cascading actions. But what they do is very simple: if you perform an update and change the value of a parent row, the value of the child row will be set to NULL, or set to the default value for the column (regardless of SET NULL or set default).

　　4, foreign key other aspects of consideration

There are only possible options in the foreign key:

1. Populate the column with values that match the corresponding columns in the referenced table.

By defining a reference column as not NULL, you can make the foreign key completely mandatory (that is, the user must reference a row of data in the table that must have a match when the data is added).

2, do not populate any values, and make the value null.

When a reference column is allowed to have a null value, the user can choose not to provide a value-even if the referenced table does not have a row that matches the null value, or if it is allowed to be inserted.

3. Unique Constraint

Unique constraints are similar to primary keys in that they all require a unique value on the column (or combination of columns) specified in the table, except that the unique constraint is not treated as a unique identifier for the record in the table (even if you can use it in this way), and you can have multiple unique constraints (only one primary key in each table) )。

Once a unique constraint is established, each value in the specified column must be unique. If you update or insert a record that has an existing value on a column with a unique constraint, SQL Server throws an error and rejects the record.

Unlike the primary key, a unique constraint does not automatically prevent setting a null value, allowing NULL to be determined by the setting of the null option for the corresponding column in the table, but even if null values are true, only one null value can be inserted in a table (if more than one is allowed).

Create a unique constraint on a table that already exists:

Alter Table Account     Add constraint Ak_accountname    - - constraint name    unique (account_name)-    -  column name

The AK represents the replacement key (Alternate key), and the unique constraint is also called the replacement key.

　　The difference between a primary key and a unique constraint:

• A PRIMARY KEY constraint does not allow null values. Index keys for any index are not allowed to contain null values. But a unique constraint allows a null value to be included, but a unique constraint treats two null values as duplicates, so each column that imposes a unique constraint is allowed to contain only one null value.
• The clustered index is automatically created when the primary key is created, unless the current table already contains a clustered index or the nonclustered keyword is specified when the primary key is created.
• A nonclustered index is automatically created when a unique constraint is created, unless you specify the clustered keyword and the current table does not have a clustered index.
• There can be only one primary key in each table, but more than one unique constraint.

SQL Server-Constraints