Shulou(Shulou.com)06/01 Report--

is to use SSMS tools to graphically create and maintain relational tables.

1. Create a table:

In SSMS, the steps for graphically creating tables are shown in the following figure,

Step 1: Open SSMS, expand the database where you need to create a table, and then right-click on the table node and select the 'New Table' command in the newly popped up list. The table designer pane will appear on the right side of the window; the effect is as shown below.

Step 2: Define the structure of the table in the Table Designer pane, setting for each field, including 'Column name',' Data type','Allow null'

Step 3: Save the table definition; the effect is as shown below (two saving methods are described below)

or

2. Definition of integrity constraints:

(1)Primary Code Constraints:

First, select the column to define the primary key, and then click the 'Set Primary Key' button on the toolbar (or right click on the column to define the primary key and select the 'Set Primary Key' command in the pop-up menu). The effect is as shown in the following figure.

Note: If you define a primary key composed of multiple columns, you must select these columns at the same time, and then click the 'Set Primary Key' button

(2)Outer code constraint:

What is the outer code?

Generally, when there are two or more tables, the primary code of one table is referenced in the other table, so it is the external code of the other table;

For example, there are two tables F1(a,b,c) and F2(x,y,z,a), where a is the main code of F1 table and x is the main code of F2, but a has a relationship in F2 table, so a is called the outer code of F2.

Steps for setting outer codes:

First, create the upper course table and sc table (the previous blog has information about these two tables), and then start defining the outer code on the basis of sc table

Step 1: Click the 'Relationship' button on the toolbar, as shown in the figure below, the dialog box below appears, and then click 'Add' at the bottom left.

Step 2: Click 'Add' to open the dialog box shown below, and then click 'Three Points' button on the right side of General-> Table and Column Specifications.

A dialog box for tables and columns appears

Step 3: Set primary key table and foreign key table in the dialog box of table and column; among them, select the table where the primary code referenced by the foreign code is located in the drop-down menu table of primary key table on the left, and then select the primary code column referenced by the foreign code in the grid below the primary key table; Similarly, select the table where the foreign code is located from the foreign key table drop-down menu on the right, and then select the primary code column referenced by the foreign code in the first row of the grid under the foreign key table (if the system automatically displays redundant columns, but the currently defined foreign code does not contain the corresponding columns, you should select 'None'), the effect is as shown in the following figure;

Step 4: After clicking OK at the bottom of the Tables and Columns dialog box, the designer of the foreign key relationship

Step 5: Click the 'Save' button on the tool list, the dialog box below appears, and then click the 'Yes' button below

(3)unique constraint: (define unique constraint on SID column in student as column)

Step 1: In the designer interface of student table, select the button 'Manage Index and Key' in the toolbar above, and the Index/Key dialog box pops up.

Step 2: Click Add at the bottom left of the Index/Key dialog box, and then click IK_Student * in the blank box on the left; then look at the [General] column on the left, and select the 'Index' option on the right of 'Type' in the drop-down list.

Step 3: Click the Sno(ASC) item under the 'Index' item, and a 'Three Dots' button will appear on the right side. Click the button to pop up the 'Index Column' dialog box; select the column to establish unique value constraints in the drop-down list of column names.

Step 4: Close the index/key dialog box and save

(4)Default constraints:

Take the Ssex column in the student table as an example: first, select the Ssex column, then enter male in the text box corresponding to 'Default value or binding' in the column properties section below, and then click Save

(5)Define check constraints:

Step 1: Click 'Manage check constraints' on the toolbar to pop up the 'check constraints' dialog box. Click the 'Add' button on this dialog box.

Step 2: Click the mouse on the blank part on the right of General-> Expression, and then click the 'Three Dots' button on the right to pop up the check constraint expression dialog box, write the check expression, and click OK.

Step 3: Save Settings

3. Modification table:

Expand the corresponding database on the table node, and then expand the table on the corresponding specific table node. Right-click and select the 'Design' command, and then you can add new columns, modify column data types, delete columns, add constraints, and delete constraints.

Note: If check and default constraints are deleted, expand the Constraint node under the table; if primary code, external code and unique value constraints are deleted, expand the Key node under the table, right-click the constraint to be deleted, and select Delete from the pop-up menu.

Special reminder: If the deleted main code constraint has foreign code reference, you must delete the corresponding foreign code first, and then delete the main code.

4. Delete table:

Expand the database where the table to be deleted is located, and then expand the 'Table' node below it. Right-click on the table to be deleted and select Delete

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.