Common constraints for SQL Server

1. PRIMARY KEY constraint------I'm the most common (PRIMARY key)

• Limit: cannot be empty, data unique, one table only one
• Method:
  • The table is built directly after the column type as follows:

    1 CREATE TABLE Studb 2 (3CHAR(PRIMARYKEY45

  • Add a statement after a table is built

    The Add statement is    : ALTER TABLE < table name >         ADDCONSTRAINT  custom constraint name  constraint type specific constraint description Example:      ALTERTABLE  studb        ADDCONSTRAINTPRIMARYKEY 

2. FOREIGN KEY constraint------deep-rooted linker between table and table (FOREIGN key)

• Effect: For establishing a relationship between two tables, you need to specify the column that references the main table

• Method:

  1when you build a table, add it directly behind the type.CREATE TABLEStudb (SIDCHAR( A), SnameVARCHAR( A)FOREIGN KEY REFERENCESfrom table name (column name))2after the table is built, add the following statements as follows:ALTER TABLEPrimary Table nameADD CONSTRAINTCustom constraint nameFOREIGN KEY(column name)REFERENCESExample from table name (column name):ALTER TABLEStudbADD CONSTRAINTDf_snameFOREIGN KEY(Sname)REFERENCESScore (Sname)

3. Unique constraint-----There is only one baby in this column (unique Key)

• Effect: The column value is unique, even if it is a null value, but also to the wayward unique
• Method:

  1when you build a table, add it directly behind the type.CREATE TABLEStudb (SIDCHAR( A), SnameVARCHAR( A), StunoVARCHAR( A)UNIQUE    )2after the table is built, add the following statements as follows:ALTER TABLEPrimary Table nameADD CONSTRAINTCustom constraint nameUNIQUE(column name) Example:ALTER TABLEStudbADD CONSTRAINTDf_stunoUNIQUE(Stu'NO)

4. Default constraint------I'll help you choose it silently (default)

• Effect: Just hey, you picked it.
• Usage:

  1. Add directly to the type after the table is builtCREATE TABLEStudb (SIDCHAR( A), SnameVARCHAR( A), StunoVARCHAR( A), SsexCHAR(2)DEFAULT 'male'    )2after the table is built, add the following statements as follows:ALTER TABLEPrimary Table nameADD CONSTRAINTCustom constraint nameDEFAULT(The default Dongdong) forexample of a column name:ALTER TABLEStudbADD CONSTRAINTDf_ssexDEFAULT('male') forSsex

5. Check the constraints------you, you're the one that's stopping me from checking. (check)

• Effect: Stop check, pass before you can enter the table
• Usage:

  1when you build a table, add it directly behind the type.CREATE TABLEStudb (SIDCHAR( A), SnameVARCHAR( A), StunoVARCHAR( A), SsexCHAR(2), SageINT CHECK(age>=  -   andAge<= -)    )2after the table is built, add the following statements as follows:ALTER TABLEPrimary Table nameADD CONSTRAINTCustom constraint nameCHECK(condition) Example:ALTER TABLEStudbADD CONSTRAINTDf_ SageCHECK(age>=  -   andAge<= -) 

6. Other constraints

• Auto-growth constraints
  • Restriction: Must be used when the type is INT,SMALLINTT, etc.

    IDENTITY (start number, each plus number) For example: Stuseat    SMALLINT  IDENTITY (1,1)

• Limit number of digits

  syntax:  column name NUMERIC (number of digits, number of decimal places) For example:    stuid  NUMERIC (0),     

  　

Common constraints for SQL Server