SQL server--constraints

　　What the constraint is:

　　　　　　　　Everyone in the website or the app registered account, in the registration of the account will limit the user name, password and other formats, if the format is not registered. In the database we can restrict by constraints, and data beyond the bounds cannot be written.

　　　　　　　　Types of Constraints:

PRIMARY KEY constraint: The value of the primary key column cannot be null and cannot be duplicated (a PRIMARY key constraint is a special unique constraint), a column with a PRIMARY KEY constraint can be referenced by another table, and a table can have only one primary KEY constraint.

　　　　　　　　　　　　　　UNIQUE constraint: data in a column with a unique constraint cannot be duplicated, but can be null (NULL).

　　　　　　　　　　　　　　Check Constraint: limits the range of values for the data in a column.

　　　　　　　　　　　　　　DEFAULT constraint: sets the column after the default constraint, and the database automatically displays the default values that we set when the data is not entered.

　　　　　　　　　　　　　　foreign Key constraints: FOREIGN KEY constraints and primary KEY constraints are used together, the data range in the FOREIGN KEY constraint column is in the primary key column of the reference, and must not exceed the primary key column's scope (that is, the data in the Foreign key column is only the value that exists in the primary key column), and the data type of the foreign key column is the same

　　How to create a constraint:

　　　　　　　　　　PRIMARY KEY constraint : Right-click the column name you want to create the primary key constraint, and then click Set Primary Key.

　　

　　　　　　　　　　　　　UNIQUE constraint : As above, right-click on the column where you want to create a unique constraint

　　

Then click on the index/key to change the index after the type to a unique piece.

　　　

　　　　　　　　　　　　　　Check Constraint : ibid., click Check Constraint

　　　　

You can create a primary key constraint by first adding and then clicking the text box after the expression.

　　　　

　　　　　　　　　　　　　　　　default constraint : In the design table below, there is a default value of the Settings box, in the following text box to add the default value.

　　　　

　　　　　　　　　　　　　　　　FOREIGN KEY constraints :

(1) Right-click the table where you want to create the constraint, and select the "Design" command from the popup shortcut menu.

(2) Right-click a column in the table, select the "relationship" command from the popup shortcut menu, pop up the "Foreign Key Relationship" dialog box, click the "Add" button to add the relationship you want to select;

(3) In the Foreign Key Relationship dialog box, click the ... button in the table and column specification text box to select the primary key table and foreign key table for which you want to create the foreign key constraint;

(4) in the Tables and Columns dialog box, set the name of the relationship, and then select the primary key table to be referenced by the foreign key and the fields used. Finally, click the OK button to return to the Foreign Key Relationships dialog box.

　　　　　　　　　　　　

　　　　Examples of common constraints:

　　　　　　　　　　PRIMARY KEY constraints: In general, the first column (number column) in a table is set as the primary key column, because SQL Server allows only one table to describe a single thing, so we simply reference the ordinal of the table's primary key column when the other table uses the foreign key to reference the table's contents.

　　Check constraints: In my homework, the most is the constraint sex can only enter male or female, code is: sex= ' or sex= ' female ' (here the definition of gender column named sex, note: The character is quoted as a single quotation mark, the logic in the database is OR and not, is not in the program | | & &! )。 There is the constraint data length, such as phone number, ID number (both of which must be unique), the length of the constraint will be used in the function Len (), the code is: Phone: Len (tel) =11 ID number: Len (ID) = 18, in parentheses is the column name.

Default constraint: Set a DEFAULT constraint simply enter the default value in the box following the default value. But to set a time to the default time (that is, the current time), you have to use the function, that is, enter in the box: getdate ().

PRIMARY KEY constraint: When creating a PRIMARY key constraint, it is important to note that (1) the object table to be referenced cannot be mistaken, (2) The primary key and foreign key correspond, and (3) the data type of the primary key and foreign key must be the same.

Want to know more QQ bar! 1289747698. Waiting for you yo!

SQL server--constraints