Key and constraint in SQLSEVER, and SQLSEVER Constraint

Key and constraint in SQLSEVER, and SQLSEVER Constraint

SQL Server has five types of constraints: PRIMARY KEY constraint, FOREIGN KEY constraint, UNIQUE constraint, DEFAULT constraint, and CHECK constraint. To view or create constraints, you must use Microsoft SQL Server Management Studio.

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 (chargesystem in my example) -> table-> the table you want to view (BasicTale in my example)-> Column

 

 

As shown in, the Primary Key has a small golden Key. ModifyIDea is the primary key of the BasicTable table.

To create a primary key constraint, right-click the table, select design, open the Table Designer, select the row, and click the golden Key above to create the Primary KEY.

 

 

 

 

You can also right-click the row you want to set as the primary key, 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 the column. The small 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 link button above to view all the Foreign Key constraints.

 

 

 

 

 

In the preceding example, OperatorID of the billTable table is a foreign key, and OperatorIDea of the OperatorIDea table is a primary key.

 

The following describes how to create the Foreign Key constraint.

After clicking the link button, select Add in the pop-up dialog box.

 

Click the button in the red box below:

 

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

 

Click OK. Do not forget to save your design.

 

3. UNIQUE constraint (UNIQUE key or index)

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.

 

Taking BillTable as an example, if we want to restrict BillIDea to a unique one, Click Manage index and key.

 

Click Add to Add the Unique constraint.

 

The selected column is BillID (ASC), which is unique.

 

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

How to Set unique key constraints for SQL Server tables?

1. If no table is created, you can add a unique behind the column in the query analyzer, which implements the uniqueness constraint.
2. You have created a table,
Alter table name
Add constraint name unique (column name [also called Field])

SQL server 2005 foreign key constraints

Alter table [tbname foreign key table] add constraint FK _ foreign key constraint name foreign key (foreign key column) references [tbname primary key table] (primary key column) -- The referenced column must be a unique key on update cascade -- Optional cascade update on delete cascade -- Optional cascade delete