SQL Advanced application-constraints (not NULL, UNIQUE, PRIMARY key, FOREIGN key, CHECK, DEFAULT)

First, SQL constraints

Constraints are used to restrict the type of data being added to the tag

　　You can specify the constraint (via the CREATE TABLE statement) when creating the table, or after the table is created (via the ALTER table statement)

The following constraints are mainly discussed:

1) Not NULL

2) UNIQUE

3) PRIMARY KEY

4) FOREIGN KEY

5) CHECK

6) DEFAULT

Second, SQL not NULL constraint

The NOT NULL constraint enforces that the column does not accept null values

A not null constraint forces a field to always contain a value . This means that if you do not add a value to a field, you cannot insert a new record or update it

The following SQL statement forces id_p and LastName columns to not accept NULL values

CREATE TABLE Persons (     id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255),    City  varchar (255))

　　

Third, SQL UNIQUE constraints

Unique constraint uniquely identifies each record in a database table

Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection

PRIMARY KEY has an automatically defined UNIQUE constraint

Note that each table can have multiple UNIQUE constraints, but only one PRIMARY KEY constraint per table

The following SQL creates a unique constraint in the "id_p" column when the "Persons" table is created:

Mysql

CREATE TABLE Persons (    id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255),    UNIQUE (id_p))

SQL Server/oracle/ms Access

CREATE TABLE Persons (    id_p int not null UNIQUE,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255),    City varchar (255))

If you need to name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax

CREATE TABLE Persons (    id_p int not null,    LastName  varchar (255) is not NULL,    FirstName  varchar ( 255),    Address varchar (255), City    varchar (255),    CONSTRAINT Uc_personid UNIQUE (id_p, LastName))

When the table has been created, if you want to create a unique constraint in the id_p column, use the following SQL

Mysql/sql Server/oracle/ms Access

ALTER TABLE personsadd UNIQUE (id_p)

To name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax

Mysql/sql Server/oracle/ms Access

ALTER TABLE personsadd CONSTRAINT uc_personid UNIQUE (id_p, LastName)

Revoke a UNIQUE constraint

To revoke a unique constraint, use the following sql:

Mysql

ALTER TABLE Personsdrop INDEX Uc_personid

SQL Server/oracle/ms Access

ALTER TABLE Personsdrop CONSTRAINT Uc_personid

　

SQL Advanced application-constraints (not NULL, UNIQUE, PRIMARY key, FOREIGN key, CHECK, DEFAULT)