Interface way to create, modify, and delete relational tables and define complete constraints on tables

is to graphically create and maintain relational tables with the SSMs tool.

1. Create a table:

In SSMs, the steps to graphically create a table are as shown in

Step One: Open SSMs, expand the database where you want to create the table, and then right-click the new Table command in the newly bounced order on the table node, and the Table Designer pane appears to the right of the window;

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8B/07/wKiom1hBmKTDmabEAAEnpZEDLO4595.png-wh_500x0-wm_3 -wmp_4-s_1766105179.png "title=" qq picture 20161202235100.png "alt=" Wkiom1hbmktdmabeaaenpzedlo4595.png-wh_50 "/>

Step two: Define the structure of the table in the Table Designer pane, set for each field, including ' column name ', ' data type ', ' Allow null '

The third step: Save the definition of the table, the effect is as follows (two methods of saving are described below)

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/07/wKiom1hBmjKT0MBnAAB0H1tr2DU605.jpg-wh_500x0-wm_3 -wmp_4-s_4105281687.jpg "title=" qq picture 20161202235630.jpg "alt=" Wkiom1hbmjkt0mbnaab0h1tr2du605.jpg-wh_50 "/>

or a

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8B/03/wKioL1hBmk-yiL2fAAB0pkSTS3Q622.jpg-wh_500x0-wm_3 -wmp_4-s_2205881733.jpg "title=" qq picture 20161202235727.jpg "alt=" Wkiol1hbmk-yil2faab0pksts3q622.jpg-wh_50 "/>

2. Define INTEGRITY constraints:

(1) Main code constraint:

First, select the column you want to define the main code for, and then click the ' Set Primary key ' button on the toolbar (or right-click on the column where you want to define the main code, select ' Set Primary key ' in the popup menu),

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8B/07/wKiom1hBnI6inXX1AACAyvVsxRg534.jpg-wh_500x0-wm_3 -wmp_4-s_1496803393.jpg "style=" Float:none; "title=" QQ picture 20161203000816.jpg "alt=" Wkiom1hbni6inxx1aacayvvsxrg534.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8B/03/wKioL1hBnI_hoe9VAACP0GcOdi8472.jpg-wh_500x0-wm_3 -wmp_4-s_2444270222.jpg "style=" Float:none; "title=" QQ picture 20161203000820.jpg "alt=" wkiol1hbni_ Hoe9vaacp0gcodi8472.jpg-wh_50 "/>

Note: If you define a master code that consists of multiple columns, you must first select the columns and then click the ' Set Primary key ' button

(2) External code constraints:

What's The outer code?

Generally in 2 or more tables of the relationship, one of the table's main code in another table is referenced, then it is another table of external code;

Example: For example, there are two tables F1 (A,B,C) and F2 (X,y,z,a), where A is the main code of the F1 table, X is the main code of F2, but a in the F2 relationship table there is a link, so this is called a F2 of the outer code

To set the outer code step:

First, create the course table and SC table (the previous blog has the information of these two tables), and then start to define the external code based on the SC table

First step: Click on the ' Relationships ' button on the toolbar, as shown in the dialog box, then click ' Add ' in the bottom left

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/07/wKiom1hCMGmTi6yMAACLsNBbzdw886.jpg-wh_500x0-wm_3 -wmp_4-s_2025578711.jpg "title=" qq picture 20161203103925.jpg "alt=" Wkiom1hcmgmti6ymaaclsnbbzdw886.jpg-wh_50 "/>

Second step: Click on the ' Add ' dialog box, then click on the ' three Dots ' button on the right side of the "General" table and column specifications.

dialog boxes for tables and columns appear

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8B/07/wKiom1hCMS_Rt8ASAAB5w97xjE8549.jpg-wh_500x0-wm_3 -wmp_4-s_2434512354.jpg "title=" qq picture 20161203102437.jpg "alt=" Wkiom1hcms_rt8asaab5w97xje8549.jpg-wh_50 "/>

The Third step: Table and Column dialog box set the primary key table and the foreign key table; In the left primary key table drop-down menu table, select the main code referenced by the External Code table, and in the bottom of the primary key table in the grid, select the main code column referenced by the external code, similarly, the right foreign key table drop-down menu table to select the Outer Code table, and then the Foreign key table below the grid, The first line selects the main code column referenced by the outer code (if the system automatically also displays the extra columns, but the currently defined outer code does not contain the corresponding columns, you should select ' None '), the effect is as follows;

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8B/04/wKioL1hCNBaTnsNrAAFiinmBKlM664.png-wh_500x0-wm_3 -wmp_4-s_1964148351.png "title=" qq picture 20161203102457.png "alt=" Wkiol1hcnbatnsnraafiinmbklm664.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8B/04/wKioL1hCNCuwLz9vAAD6yWoQ-7w737.png-wh_500x0-wm_3 -wmp_4-s_3569610754.png "title=" qq picture 20161203102515.png "alt=" Wkiol1hcncuwlz9vaad6ywoq-7w737.png-wh_50 "/>

Fourth step: The designer of the foreign key relationship after clicking ' OK ' under the Table and Column dialog box

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8B/04/wKioL1hCNNeyGUbUAACR-8aqsIs507.jpg-wh_500x0-wm_3 -wmp_4-s_512942541.jpg "title=" qq picture 20161203102533.jpg "alt=" Wkiol1hcnneygubuaacr-8aqsis507.jpg-wh_50 "/>

Fifth step: Click on the "Save" button on the tool list, the dialog box appears, then click the ' Yes ' button below

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/07/wKiom1hCNQug6Rf2AAFWC3Hv3NY326.png-wh_500x0-wm_3 -wmp_4-s_2857753657.png "title=" qq picture 20161203102546.png "alt=" Wkiom1hcnqug6rf2aafwc3hv3ny326.png-wh_50 "/>

(3) Unique constraint: (defines a unique constraint as a column on the SID column in student)

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

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/04/wKioL1hCOQCTo1RkAACHES669Ro401.jpg-wh_500x0-wm_3 -wmp_4-s_1419409416.jpg "title=" qq picture 20161203111524.jpg "alt=" Wkiol1hcoqcto1rkaaches669ro401.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/04/wKioL1hCORTDn0mSAAF2dEN2zNI750.png-wh_500x0-wm_3 -wmp_4-s_1357659656.png "title=" qq picture 20161203111546.png "alt=" Wkiol1hcortdn0msaaf2den2zni750.png-wh_50 "/>

Second step: At the bottom left of the Index/Key dialog box, click Add, the left side of the blank box more than a ik_student*, and then click ik_student*; then look at the "General" column on the left, respectively, the ' type ' to the right of the ' index ' option in the drop-down list select ' Unique key '

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8B/04/wKioL1hCSpfy94-nAAFnKURIeFo053.png-wh_500x0-wm_3 -wmp_4-s_2182597397.png "title=" qq picture 20161203111553.png "alt=" Wkiol1hcspfy94-naafnkuriefo053.png-wh_50 "/>

Step three: Then click on the Sno (ASC) entry under the ' Index ' entry, it will have a ' three dots ' button on the right, and click the button to pop up the ' Indexed Columns ' dialog box; in the drop-down list of column names, select the column to create a unique value constraint

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8B/08/wKiom1hCTMmzIl5UAADhKnZEiL0421.png-wh_500x0-wm_3 -wmp_4-s_66502484.png "title=" qq picture 20161203111558.png "alt=" Wkiom1hctmmzil5uaadhknzeil0421.png-wh_50 "/>

Fourth step: Close the Index/Key dialog box and save

(4) Default constraint:

As an example of ssex in the student table: First, select the Ssex column, and then enter the "Default value or bindings" text box in the following Column Properties section for the male, and then click Save

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8B/08/wKiom1hCTobT_hxCAACVIq27e90368.jpg-wh_500x0-wm_3 -wmp_4-s_1046890107.jpg "title=" qq picture 20161203124753.jpg "alt=" Wkiom1hctobt_hxcaacviq27e90368.jpg-wh_50 "/>

(5) Define a CHECK constraint:

First step: Click Manage Check constraints on the toolbar to pop up the Check Constraint dialog box, where you click the Add button

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/08/wKiom1hCT5bTuw_pAACbSsFR-QU962.jpg-wh_500x0-wm_3 -wmp_4-s_4006710182.jpg "title=" qq picture 20161203125225.jpg "alt=" Wkiom1hct5btuw_paacbssfr-qu962.jpg-wh_50 "/>

Second step: Click on the blank part to the right of the "general" expression, then click on the ' three Dots ' button on the right, pop up the CHECK Constraint Expression dialog, write the check expression, then confirm

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8B/04/wKioL1hCUJzDhMjjAACuqwodzWQ533.jpg-wh_500x0-wm_3 -wmp_4-s_2659110016.jpg "title=" qq picture 20161203125611.jpg "alt=" Wkiol1hcujzdhmjjaacuqwodzwq533.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8B/08/wKiom1hCUKvh8XfqAAFklwlgZ9A849.png-wh_500x0-wm_3 -wmp_4-s_418159151.png "title=" qq picture 20161203125616.png "alt=" Wkiom1hcukvh8xfqaafklwlgz9a849.png-wh_50 "/>

Step Three: Save Settings

3, modify the table:

Expand the corresponding database on the node of the table, and then expand the table on the corresponding table node, right-click the ' Design ' command, then you can add new columns, modify the column data type, delete columns, add constraints, delete constraints

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8B/04/wKioL1hCUibCXa3gAADAModiqgY810.jpg-wh_500x0-wm_3 -wmp_4-s_931748690.jpg "title=" qq picture 20161203130317.jpg "alt=" Wkiol1hcuibcxa3gaadamodiqgy810.jpg-wh_50 "/>

Note: If you are deleting a check, default constraint, expand the Constraints node under the table, if you delete the main code, the outer code, and the unique value constraint, expand the key node under the table, and then right-click on the constraint you want to delete and select Delete command from the popup menu

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

4. Delete the table:

Expand the database for the table you want to delete, expand the node under ' Tables ' under it, right-click on the table you want to delete, and select the Delete command

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/08/wKiom1hCVMqAtZeeAADIw45-wqc181.jpg-wh_500x0-wm_3 -wmp_4-s_3538125771.jpg "title=" qq picture 20161203131438.jpg "alt=" Wkiom1hcvmqatzeeaadiw45-wqc181.jpg-wh_50 "/>

This article from "A Growing small Tree" blog, reproduced please contact the author!

Interface way to create, modify, and delete relational tables and define complete constraints on tables