SQL constraints 2009-04-2709: 29 constraints mainly include: NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECKDEFAULT1 and notnull: The content used to control fields must not be NULL ). Usage: CreatetableMyTable (idvarchar (32) notnull, namevarchar (32) 2. Uniq
SQL constraints mainly include: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT 1, not null: The content used to control fields must NOT be blank (NULL ). Usage: Create table MyTable (id varchar (32) not null, name varchar (32) 2, Uniq
SQLConstraintsExplanations
ConstraintsIt mainly includes:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
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 fields.Constraints.
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:
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 ,.....)
)
Note: The first method (that is, directly add a field after the field)Constraints) To ensure the data uniqueness of each field.
The second method (that is, adding several fields at the same timeConstraints). This ensures that the data of several fields is Unique at the same time. For example, the Unique (id, name) field isConstraintsIf the id has a duplicate value and the name does not have a duplicate value, it is not allowed only when both fields are duplicated with the original data.
Delete Unique on SQL Server, Oracle, and MS AccessConstraintsSyntax: drop constraintUniqueName;
Delete Unique in My SQLConstraintsSyntax: drop index UniqueName;
3. Primary Key: the content of the control field cannot be repeated, but only one table can appear.
The foreign key of this table must be the primary key of another table!
A table must have at least one row of data before it can be created.
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 KeyConstraintsSyntax:
Alter table myTB1
ADD Primary Key (id,...) -- in this way, the system will customizeConstraintsName
Alter table myTB1
Add Constaint PrimaryName primary key (id) -- you can customize this method.ConstraintsName
Delete the existing Primary Key of the table on SQL Server, Orcale, and MS Access.ConstraintsSyntax:
Alter table myTB1
Drop Constraint PrimaryName
Delete an existing Primary Key in the Table in My SQLConstraintsSyntax:
Alter table myTB1
Drop Primary Key
Unique and Primary are the same: UNIQUE and PRIMARY KEYConstraintsEach column or column set provides a unique guarantee.
The difference between Unique and Primary: Each table can have multiple UNIQUEConstraintsBut each table can have only one primary key.Constraints, Unique allows NULL values, while Primary key does not.
Note: In the same database, even in different tables,ConstraintsNames cannot be the same.
4. Foreign Key: FOREIGN KEYConstraintsUsed to prevent the action of damaging the connection between tables, FOREIGN KEYConstraintsIt can also prevent illegal data from being inserted into a foreign key column 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 a foreign key to the tableConstraintsSyntax:
Alter table myTB1
Add foreign key (id) references myTB (id) -- this way, the write system will customizeConstraintsName
Alter table myTB1
Add Constraint foreignName foreign key (id) references myTB (id) -- this way you can customizeConstraintsName
Delete Foreign keys in SQL Server, Orcale, and MS AccessConstraintsSyntax:
Alter table myTB1
Drop Constraint foreignName;
Delete Foreign keys in My SQLConstraintsSyntax:
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 checkConstraintsSyntax:
Alter table myCheck
Add check (id = 'cel'); -- This defines the system custom check.ConstraintsName.
Alter table myCheck
Add constraint checkName check (id = 'cel'); -- you can customize the checkName by yourself.ConstraintsName.
Delete an existing check in a table on SQL Server, Orcale, or MS Access.ConstraintsSyntax:
Alter table myCheck
Drop constraint checkName
6. Default: Set the Default value for the new record.
Add default in SQL Server, Orcale, MS Access, and My SQLConstraintsSyntax:
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
