Mssqlserver constraints

There are several constraints:

Not null: the content used to control fields must NOT be NULL ).

UNIQUE: The control field content cannot be repeated. A table can have multiple Unique constraints.

Primary key: it is also used to control the content of fields that cannot be repeated, but it can only appear in one table.

Foreign key: the foreign key constraint is used to prevent the action of damaging the connection between tables. The foreign key constraint can also prevent illegal data insertion into the foreign key column, because it must be one of the values in the table to which it points.

CHECK: used to control the value range of a field.

DEFAULT: set the DEFAULT value of the new record.

1. not null: the content used to control the field must not be NULL ).

Usage: Create table MyTable
               (
Id varchar (32) not null,
Name varchar (32)
               )
2. Unique: the control field content cannot be repeated. A table can have multiple Unique constraints.

NEW:
Add the Unique syntax supported by SQL Server, Orcale, and MS Access:
Create table MyTable
                (
Id varchar (32) not null unique,
Name varchar (32)
                )
Add the Unique syntax supported by SQL Server and My SQL: you can add several fields as constraints at the same time, such as: unique (id, name)
Create table MyTable
                (
Id varchar (32) not null,
Name varchar (32 ),
Unique (id ,.....)
                 )
Add the Unique syntax supported by SQL Server, Orcale, MS Access, and My SQL:
Create table MyTable
                  (
Id varchar (32) not null,
Name varchar (32 ),
Constraint uniqueName unique (UniqueColumn ,.....)
                   )

Delete:
Delete the Unique constraint syntax in SQL Server, Oracle, and MS Access: drop constraint UniqueName;
Delete Unique constraint syntax in My SQL: drop index UniqueName;

Modify:

Alter table EPlatform
Add constraint Unique_EPlatform
Unique ([UserId], [Platform]);

 

3. Primary Key: the content of the control field cannot be repeated, but only one table can appear.
Add the Primary Key syntax supported by SQL Server, Orcale, and MS Access:
Create table myTB1
             (
Id nvarchar (32) not null primary key,
Name nvarchar (32)
              )
Add the Primary Key syntax supported by SQL Server and My SQL:
Create table myTB1
             (
Id nvarchar (32) not null,
Name nvarchar (32 ),
Primary key (id)
             )
Add the Primary Key syntax supported by SQL Server, Orcale, MS Access, and My SQL:
Create table myTB1
             (
Id nvarchar (32) not null,
Name nvarchar (32 ),
Constraint PrimaryName primary key (id)
              )
When SQL Server, Orcale, MS Access, and My SQL tables already exist, add the table's Primary Key constraint syntax:
Alter table myTB1
ADD Primary Key (id,...) -- In this way, the system will customize the constraint name.
Alter table myTB1
Add Constaint PrimaryName primary key (id) -- You can customize the constraint name.
Delete the existing Primary Key constraint syntax in the SQL Server, Orcale, and MS Access tables:
Alter table myTB1
Drop Constraint PrimaryName
Delete the existing Primary Key constraint syntax in My SQL:
Alter table myTB1
Drop Primary Key
Unique shares the same with Primary: UNIQUE and primary key constraints provide uniqueness guarantee for columns or column sets.
Unique differs from Primary: each table can have multiple UNIQUE constraints, but each table can have only one primary key constraint, and Unique can have NULL values, primary key cannot have NULL values.
Note: In the same database, even in different tables, the constraint names cannot be the same.
4. The Foreign Key: foreign key constraint is used to prevent table connection failure. The foreign key constraint can also prevent illegal data insertion into Foreign Key columns, because it must be one of the values in the table to which it points.
Add the Foreign Key syntax supported by SQL Server and My SQL:
Create table myTB1
              (
Id nvarchar (32) not null primary key,
Name nvarchar (32 ),
Foreign key (id) references myTB (id)
              )
Add the Foreign Key syntax supported by SQL Server, Orcale, and MS Access:
Create table myTB1
              (
Id nvarchar (32) not null foreign key references myTB (id ),
Name nvarchar (32)
              )
Add the Foreign Key syntax supported by SQL Server, Orcale, MS Access, and My SQL:
Create table myTB1
              (
Id nvarchar (32) not null primary key,
Name nvarchar (32 ),
Constraint foreignName foreign key (id) references myTB (id)
              )
When SQL Server, Orcale, MS Access, and My SQL tables already exist, add the syntax for foreign key constraints to the table:
Alter table myTB1
Add foreign key (id) references myTB (id) -- In this way, the write system will customize the constraint name.
Alter table myTB1
Add Constraint foreignName foreign key (id) references myTB (id) -- so that you can customize the Constraint name.
Syntax for deleting foreign key constraints in SQL Server, Orcale, and MS Access:
Alter table myTB1
Drop Constraint foreignName;
Syntax for deleting foreign key constraints in My SQL:
Alter table myTB1
Drop foreign key foreignName;
5. Check: used to control the field value range.
Add the check syntax supported by SQL Server and My SQL:
Create table myCheck
              (
Id nvarchar (32) not null,
Age int not null,
Check (age> 15 and age <30)
              )
Add the check syntax supported by SQL Server, Orcale, and MS Access:
Create table myCheck
              (
Id nvarchar (32) not null,
Age int not null check (age> 15 and age <30)
              )
Add the check syntax supported by SQL Server, Orcale, MS Access, and My SQL:
Create table myCheck
              (
Id nvarchar (32) not null,
Age int not null,
Constraint checkName check (age <15 and age> 30)
              )
When SQL Server, Orcale, MS Access, and My SQL tables already exist, add the check constraint syntax to the table:
Alter table myCheck
Add check (id = 'cel'); -- this is the name of the system-defined check constraint.
Alter table myCheck
Add constraint checkName check (id = 'cel'); -- you can customize the name of the check constraint.
Syntax for deleting an existing check constraint in a table on SQL Server, Orcale, or MS Access:
Alter table myCheck
Drop constraint checkName
6. Default: set the Default value for the new record.
Add the default constraint syntax in SQL Server, Orcale, MS Access, and My SQL:
Create table myDefault
              (
Id int,
Name nvarchar (32) default 'cel'
              )
Add the default field value to an existing table in My SQL:
Alter table myDefault
Alter [id] set default 0
Add the default values to the existing tables of SQL Server, Orcale, and MS Access:
Alter table myDefault
Alter column [id] set default 0
Delete the default field syntax in My SQL:
Alter table myDefault
Alter ColumnName drop default