SQL Server Visual Operations database table

First, the constraint classification (to ensure the integrity of the data).

A, PRIMARY KEY constraint--entity

Set Primary key cannot be null value, have unique identification, is to prevent typos, set the primary key, slowing the speed of new data, but greatly improve the efficiency of the query. 1. Not heavy. 2. Not available. 3. Sort. 4. Unique (cannot appear two primary keys) 5. Combine primary keys.

To build the primary key:

1. Visually build the primary key. In Object Explorer, select the table in which you want to set the primary key, right-click Design, and in the popup window, right-click the small triangle in front of the column name in the Table Designer-select "Set Primary Key".

2. Code-building the primary key. Primary key

B, FOREIGN KEY constraint--reference

Two tables, Main table and from table. Main Table--use the primary key to constrain each other. From a table-a constrained table, a constrained column is called a foreign-key column. The content that is filled out from the foreign key table must be what is already in the primary key of the main table.

Build foreign Key:

1. Visualize the operation:

The first step is to right-click the small triangle in front of the column name in the table designer that you want to be the foreign key, and the second step: Pop up the foreign Key Relationships window and click here in table and column specification.

2. Code: References primary table name (primary key column)

C, non-null constraints--cannot be null

1. Visualization: Table Design interface, each column has a "Allow null" check box.

2. Code: NOT NULL

D, self-growth

It has a "seed" (the starting value) and a "step" (quantity per increment).

Attention:

1. Each self-growth value, once used, is obsolete and will not be reused.

2. Any behavior that adds a value to the self-growing column is incorrect.

3. There are type requirements for columns: int decimal

1. Visualize: In the properties of a column, identify the specification-yes.

2. Code: Identity (A, b)-there is a fixed index, the previous a is the beginning of a self-growth from a, followed by a number of B refers to growth;

Example: Code int primary identity (1,-1)

E, check Constraint

The main thing is to further standardize the values inside the column.

1. Visualize: Right-click the--check constraint on the column--Add a CHECK constraint in the popup dialog box.

2. Code: Check (expression)

F, default value

1. Visualization: Properties in a column--default value or binding

2. Code: Default Value

G, UNIQUE constraint

Can be empty, but not heavy.

1. Visualization: Right-click on the column select-Index/Key-in this interface can be both indexed and unique constraints can be built.

2. Code: Unique

H, Index

Improve the efficiency of your queries. A table can establish multiple indexes on different columns. Cluster index (sort, primary key), non-clustered index (normal index). The disadvantage of index: the efficiency of increase, deletion and change will be reduced.

1. Visualization: Similar to the method of building a unique constraint.

2. Code: CREATE index index name on table name (column name)

Second, the View

Wrap the query as if it were a table. The view itself does not store data.

Views can be set up from one table, multiple tables, multiple tables, and views.

Benefits of the view: easy to view.

Disadvantages: increase, deletion, change inconvenient.

1. Visualization: Object Explorer, database-View right-click-New View.

2. Code: CREATE view name as SQL query statement, group, sort, in etc can not write

View Usage: SELECT * from view name

SQL Server Visual Operations database table