Five SQL database constraints

#Five constraints

　　1. Primay Key Coustraint uniqueness, non-emptiness　　

       2. Unique Counstraint uniqueness, can be empty, but there can only be one

　　3. Check Counstraint limits the range and format of the column data (such as age, gender, etc.)　　

       4. Default Counstraint the default value of the data　　

       5. Foreign Key Counstraint needs to establish a relationship between the two tables and reference the columns of the main table

#Five constraint syntax examples

1. Add a primary key constraint (using UserId as the primary key)

　　alter table UserId　　add constraint PK_UserId primary key (UserId)

2. Add a unique constraint (the ID number is unique because everyone’s is different)

　　alter table UserInfo　　add constraint UQ_IDNumber unique(IdentityCardNumber)

3. Add default constraints (if the address is not filled in, the default is "unknown address")

　　alter table UserInfo　　add constraint DF_UserAddress default (‘Unknown address’) for UserAddress

4. Add check constraints (limit the age between 20-40 years old)

　　alter table UserInfo　　add constraint CK_UserAge check (UserAge between 20 and 40)

　　alter table UserInfo　　add constraint CK_UserSex check (UserSex=’Male’ or UserSex=’Female′)

5. Add foreign key constraints (the main table UserInfo and the slave table UserOrder establish a relationship, and the associated field UserId)

　　alter table UserOrder　　add constraint FK_UserId_UserId foreign key(UserId)references UserInfo(UserId)

#SQL Server Detailed Explanation of the Five Major Constraints

Constraint is a method provided by Microsoft SQL Server to automatically maintain the integrity of the database. It defines the constraints that can be entered into the table or the data in a single column of the table. There are five types of constraints in SQL Server: Primary Key Constraint, Foreign Key Constraint, Unique Constraint, Check Constraint and Default Constraint .

1. Primary keyword constraints

The primary key constraint specifies that the value of a column or a combination of several columns in the table is unique in the table, that is, it can uniquely specify a row of records. Only one column in each table can be designated as the primary key, and columns of type IMAGE and TEXT cannot be designated as the primary key, and the designated primary key column is also not allowed to have NULL attributes.

There should be an explanation here: the primary key composed of multiple columns is called the combined primary key, and the combined primary key constraint can only be set as a table-level constraint; the primary key composed of a single column can be set as a column-level constraint or a table-level constraint.

Combined primary key:

Combined primary key is to use 2 or more fields to form the primary key. Use the fields contained in this primary key as the primary key, this combination is unique in the data table, and the primary key index is added.

It can be understood that, for example, there are many fields in your order table. In general, it is enough to have an order number bill_no as the primary key. However, now there may be supplementary orders and the same order number is used, then use it alone at this time The order number is not enough, because there will be repetitions. Then you can use another order serial number bill_seq as the difference. Set bill_no and bill_seq as the combined primary key. Even if the bill_no is the same, the bill_seq is different.

　　# The syntax for defining the primary key constraint is as follows:

CONSTRAINT constraint_namePRIMARY KEY [CLUSTERED | NONCLUSTERED](column_name1[, column_name2,…,column_name16])

　　#The description of each parameter is as follows:

　　　　constraint_name
　　　　specifies the name of the constraint. The name of the constraint. Should be unique in the database. If not specified, the system will automatically generate a constraint name.　　　　CLUSTERED | NONCLUSTERED
　　　　specifies the index category, and CLUSTERED is the default value.　　　　

              column_name
　　　　specifies the column names that make up the primary key. The primary key consists of up to 16 columns.

　　#Example:

1 CREATE TABLE [dbo].[UserInfo](2     [UserId] [int] NOT NULL,3     [UserName] [nvarchar](50) NOT NULL,4     CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 5     (6         [UserId] ASC,7         [UserName] ASC8     )9 ) ON [PRIMARY]

2. Foreign keyword constraints

foreign key constraints define the relationship between the tables. When a column or a combination of multiple columns in a table has the same definition as the primary key in other tables, you can define these columns or combinations of columns as foreign keys, and set it suitable for which columns in which table Associated. In this way, when the column value is updated in the table defining the primary key constraint, the foreign key columns in the tables that have associated foreign key constraints in other tables will also be updated accordingly. The effect of the foreign key constraint is also reflected in the fact that when inserting data into a table containing a foreign key, if the column of the associated table does not have the same value as the inserted foreign key column value, the system will refuse to insert data. As with the primary key, you cannot use a column defined as a TEXT or IMAGE data type to create a foreign key. The foreign key consists of up to 16 columns.

 　　#The syntax for defining foreign keyword constraints is as follows:

CONSTRAINT constraint_nameFOREIGN KEY （column_name1[, column_name2,…,column_name16]）REFERENCES ref_table [ （ref_column1[,ref_column2,…, ref_column16] ）][ ON DELETE { CASCADE | NO ACTION } ][ ON UPDATE { CASCADE | NO ACTION } ] ][ NOT FOR REPLICATION ]

#The description of each parameter is as follows:

　　　　REFERENCES

　　　　 specifies the information of the table to be associated.

　　　　Ref_table

　　　　 specifies the name of the table to be associated.

　　　　Ref_column

　　　　 specifies the name of the related column in the table to be associated.

　　　　ON DELETE {CASCADE | NO ACTION}
　　　　     specifies the related operations to the related table when deleting data in the table. In the case of a data row in the child table associated with the corresponding data row in the parent table, if the value CASCADE is specified, the corresponding data row in the child table will be deleted when the parent table data row is deleted; if the specified value is NO ACTION, SQL Server will generate an error and roll back the delete operation in the parent table. NO ACTION is the default value.

　　　　ON UPDATE {CASCADE | NO ACTION}
　　　　      specifies the related operations on the associated table when the data in the table is updated. In the case of a data row in the child table associated with the corresponding data row in the parent table, if the value CASCADE is specified, the corresponding data row in the child table will be updated when the parent table data row is updated; if the specified value is NO ACTION, SQL Server will generate an error and roll back the update operation in the parent table. NO ACTION is the default value.

　　　　NOT FOR REPLICATION
　　　　　　The foreign key constraint of the specified column has no effect when inserting data copied from other tables into the table.

 　　#Example:

1 CREATE TABLE [dbo].[UserOrder](2     [OrderId] [int] NOT NULL,3     [UserId] [int] NOT NULL,4     [UserName] [nvarchar](50) NOT NULL,5     CONSTRAINT fk_userid_username FOREIGN KEY([UserId],[UserName]) REFERENCES UserInfo(UserId,UserName) ON DELETE CASCADE,6 ) ON [PRIMARY]

3. Unique constraints

Uniqueness constraint specifies the uniqueness of the combination of one or more columns to prevent duplicate values from being entered in the column. The column specified by the unique constraint can have a NULL attribute. Because the primary key value is unique, the primary key column can no longer set unique constraints. The uniqueness constraint consists of up to 16 columns.

　　#The syntax for defining unique constraints is as follows:

CONSTRAINT constraint_nameUNIQUE [CLUSTERED | NONCLUSTERED](column_name1[, column_name2,…,column_name16])

 　　#Example:

1 create table employees (2     emp_id char(8),3     emp_name char(10) ,4     emp_cardid char(18),5     constraint pk_emp_id primary key (emp_id),6     constraint uk_emp_cardid unique (emp_cardid)7 ) on [primary]

4. Check constraints

Check constraints set check conditions on the input column or the value in the entire table to limit the input value and ensure the data integrity of the database. You can set up a compound check for each column.

　　# The syntax for defining check constraints is as follows:

CONSTRAINT constraint_nameCHECK [NOT FOR REPLICATION](logical_expression)

      #The description of each parameter is as follows:

　　　　 NOT FOR REPLICATION

　　　　　　 specifies that the check constraint has no effect when inserting data copied from other tables into the table.

　　　　Logical_expression

　　　　　　 specifies that the return value of the logical condition expression is TRUE or FALSE.

　　#Example:

1 create table orders(2     order_id char(8),3     p_id char(8),4     p_name char(10) ,5     quantity smallint,6     constraint pk_order_id primary key (order_id),7     constraint chk_quantity check (quantity>=10) ,8 ) on [primary]

　　 Note: You can't make any constraints other than check constraints on calculated columns.

5. Default constraints

The default constraint specifies the default value of the column by defining the default value of the column or using the default value of the database to bind the table column. SQL Server recommends using default constraints instead of defining default values to specify default values for columns.

 　　#The syntax for defining default constraints is as follows:

CONSTRAINT constraint_nameDEFAULT constant_expression [FOR column_name]

　　#Example:

1 CREATE TABLE [dbo].[Students](2     [Id] [int] NOT NULL,3     [Name] [nchar](10) NULL,4     [Age] [int]5 ) ON [PRIMARY]6 7 GO8 ALTER TABLE [dbo].[Students] ADD  DEFAULT ('Unknown') FOR [Name]9 GO

1 alter table [dbo].[Students] add Sex char(2) default 'Male'2 3 alter table [dbo].[Students] add constraint DF_age_Students default(20) for age

6. Column constraints and table constraints

For the database, constraints are divided into column constraints (Column Constraint) and table constraints (Table Constraint).

Column constraints as part of the column definition only act on the column itself. As part of the table definition, table constraints can act on multiple columns.

 　　1, the four items of primary key, foreign key, unique, and check can be used to create column constraints or table constraints. The default and non-empty can only create column constraints.

 　　Example:

1 create table productsss (2     p_id char(8) ,3     p_name char(10) ,4     price money default 0.01 ,5     quantity smallint check (quantity>=10) , /* olumn constraints */6     constraint pk_p_id_name primary key (p_id, p_name) /* table constraints */7 )

7. Other operations on constraints

　　#Delete constraints

ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;

　　#Close constraints

ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE;   //If it is not quoted, the CASCADE keyword is not required

　　#Open constraints

ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; //Note that opening a previously closed referenced primary key constraint does not automatically open the related foreign key constraint

Note:

　　 1. Adding a primary key constraint will automatically create a unique index. If the focused index has not been created in the table, the focused unique index is automatically created. If a focused index already exists in the table, a non-focused index is automatically created.

　　 2. Adding a unique constraint will automatically create a unique index. If you do not add [nonclustered｜clustered] after the unique keyword, a non-focused index will be created by default.