Table-level constraints and column-level constraints in SQL

Table-level constraints and column-level constraints in SQL, in SQL Server,

(1) The constraint of the base table is divided into column and table constraints
constraints are the range of values that restrict the data entered into the table by the user, generally divided into column-level constraints and table-level constraints. There are six types of column-level constraints: Primary key primary key, foreign key foreign key, unique unique, check CHECCK, default defaults, non-null/null value not null/null
table-level constraint four: primary key, foreign key, unique, check
A
column constraint is a constraint on a particular column, which is included in the column definition, is separated by a space after the other definition of the column, does not have to specify a column name, and the
Table constraint is independent of the column definition, not included in the column definition, and is typically used to constrain multiple columns together with the column definition ', ' delimited, When you define a table constraint, you must indicate the names of those columns you want to constrain. The basic syntax format for integrity constraints is:
[CONSTRAINT < constraint name >] < constraint type;
Constraint name: When a constraint does not specify a name, the system is given a name.

(2) The difference between a column-level constraint and a table-level constraint
If the integrity constraint involves more than one property column for the table, you must define it at the table level, or you can define at the column level or at the table level.
In short:
Column-level constraints: column-level constraints are part of the row definition and can be applied to only one column.
Table-level constraints: Table-level constraints are column-independent definitions that can be applied to multiple columns in a table.

(3) column-level constraints and table-level constraints in SQL usage (that is, how constraints are defined in SQL)
define constraints when creating tables:
CREATE TABLE table_name
({<column_definition > -------Column-level constraints define
|column_name as computed_column_expression-------computed column definition
|<teble_constraint>  ------table-level constraint definition
}[,.... n]
)


is a constraint defined as a column-level constraint or a table-level constraint??? The
is determined according to the actual needs and designers ' ideas. For example, primary key, which is defined as a column-level constraint when only one column is involved, is defined as a table-level constraint when multiple columns are involved. When the
Prinmary key is defined as a column-level constraint, the corresponding SQL statement:

The primary key Pk_1 established on the STU_ID is a column-level constraint

CREATE TABLE Student

(stu_id int constraint Pk_1 primary key,

Stu_name varchar (8),

....)

When defined as a table-level constraint, the corresponding SQL statement:

CREATE TABLE Student

(stu_id int not NULL,

Table-level constraints on the pk_1 of the primary key established on the STU_ID

Stu_name varchar (8),

Constraint Pk_1 primary KEY (STU_ID),

....)

Specific instructions for each constraint:

1. PRIMARY KEY constraints

PRIMARY key Constraint

The PRIMARY key constraint is used to define the primary key of the base table, to uniquely identify the function, its value cannot be null, nor can it be duplicated to guarantee the integrity of the entity.

PRIMARY key is similar to a unique constraint by creating a unique index that guarantees the uniqueness of the base table's values in the primary key column, but there is a big difference between them:

① You can define only one primary KEY constraint in a basic table, but you can define multiple unique constraints;

② for a combination of one or more columns that are specified as primary key, none of the columns can have null values, and NULL is allowed for unique keys constrained by unique.

Note: You cannot define both a unique constraint and a PRIMARY KEY constraint for the same column or set of columns.

PRIMARY key can be used for both column constraints and table constraints.

PRIMARY key is used to define a column constraint in the following syntax format:

CONSTRAINT < constraint name > PRIMARY KEY

When PRIMARY key is used to define a table constraint, the combination of some columns is defined as the primary key with the following syntax:

[CONSTRAINT < constraint name;] S PRIMARY KEY (< column name >[{< column name;}])

2. Uniqueness (unique) constraints

A unique constraint is used to indicate that the value of a base table on a combination of one or more columns must be unique.

Those columns that define a UNIQUE constraint are called unique keys, and the system automatically establishes unique indexes for unique keys, guaranteeing uniqueness of unique keys.

The unique key is allowed to be empty, but the system is guaranteed to be unique, with a maximum of one null value.

Unique can be used for both column constraints and table constraints.

The unique syntax for defining a column constraint is as follows:

[CONSTRAINT < constraint name;] UNIQUE

Uniqueness constraints are used to specify that the value of a combination of one or more columns is unique to prevent duplicate values from being entered in the column. There are several factors to consider when using uniqueness constraints:

① fields that use uniqueness constraints are allowed to be null values.

② multiple uniqueness constraints can be allowed in a table.

③ can define uniqueness constraints on multiple fields.

④ uniqueness constraints are used to force the creation of a unique index on a specified field.

⑤ By default, the index type that is created is non-clustered index.

Unique constraints are used to ensure that the data on the PRIMARY KEY constraint column is not unique.

The difference between unique and primary key is:

(1) Unique constraints are primarily used on one or more columns of a non-primary key to require data to be unique.

(2) A UNIQUE constraint allows null values to exist on the column, and the primary key is never allowed to occur.

(3) Multiple unique constraints can be created in one table, and only one primary key is set on a table

3. Check constraints

A check constraint is used to check the range allowed for a field value, such as a field that can enter only integers, and is limited to an integer of 0-100 to guarantee the integrity of the domain.

Check can be used for both column constraints and table constraints.

Its syntax format is:

[CONSTRAINT < constraint name;] CHECK (< conditions >)

A column-level CHECK constraint can only be related to a restricted field; A table-level CHECK constraint can only be related to a field in a restricted table.

You can define multiple check constraints in a table.

Only one check constraint can be defined per field in each CREATE TABLE statement.

When you define a check constraint on more than one field, you must define a CHECK constraint as a table-level constraint.

When an INSERT statement or an UPDATE statement is executed, the CHECK constraint validates the data.

The check constraint cannot contain subqueries.

4. Default Constraints

When using default constraints, you should be aware of the following points:

Only one default constraint can be defined per field.

If the default value defined is longer than the allowable length of its corresponding field, the default value entered into the table is truncated.

cannot be added to a field with an identity attribute or a data type of timestamp.

If the field is defined as a user-defined data type, and there is a default binding to that data type, the field is not allowed to have a default constraint.

5. FOREIGN KEY constraints

A FOREIGN KEY constraint is used to enforce referential integrity, providing referential integrity for a single field or multiple fields. The FOREIGN key constraint specifies a column or set of columns as the foreign key, where the table that contains the foreign key is called from the table (reference table), and the table that contains the primary key or unique key referenced by the foreign key is called the primary table (the referenced table).

The system guarantees that the value from the table on the foreign key is either a primary key value or a unique key value in the primary table, or a null value. This guarantees a connection between two tables, ensuring referential integrity for the entity.

FOREIGN key can be used for both column constraints and table constraints.

Its syntax format is:

[CONSTRAINT < constraint name;] FOREIGN KEY REFERENCES < Main Table name > (< column name >[{< column name;}])

When using foreign key constraints, you should consider the following factors:

The ① FOREIGN KEY constraint provides field referential integrity.

② the number of fields in the foreign key clause and the data type specified for each field must match the field in the References clause.

③ FOREIGN KEY constraints do not automatically create indexes and require users to create them manually.

④ the user wants to modify the data of the foreign KEY constraint, there must be select or references permissions on the table referenced by the FOREIGN KEY constraint.

⑤ you reference a field in the same table, you must use only the REFERENCES clause, and you cannot use a FOREIGN key clause.

⑥ you can have up to 31 foreign key constraints in a table.

⑦ in temporary tables, you cannot use foreign key constraints.

The data type of the ⑧ primary key and the foreign key must be strictly matched.

6. NULL Constraint

(1) Null/not NULL

Whether the value of this field is allowed is null.

The null value is either 0 or blank, not the string "NULL", but rather the meaning of "not knowing", "indeterminate", or "no data".

When the value of a field must be entered to make sense, it can be set to not NULL.

If null values are not allowed in the primary key column, the role of uniquely identifying a record is lost

Can only be used to define column constraints,

The syntax format is as follows:

[CONSTRAINT < constraint name >] [null| Not NULL]

Table-level constraints and column-level constraints in SQL