MySQL

SQL Constraint

Kevin Li
by Kevin Li 4:05 AM


 

Constraint is used to conditionally restrict which data can be stored in the data table, that is, to constrain the fields. These restrictions can be specified in CREATE TABLE when creating the data table , or modify the ALTER TABLE specification later .

What types of restrictions does SQL have?


  • NOT NULL non-empty value restriction

SQL NOT NULL Constraint

NOT NULL is used to restrict the field from accepting null values, and by default, a field is allowed to accept null values.

When a field is restricted to NOT NULL, the field must have a value when adding data.

For example, we create a customer data table and restrict its C_Id and Name field values ​​to not be empty:

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20)
);

  • UNIQUE only restriction

UNIQUE Constraint (SQL UNIQUE Constraint)

UNIQUE is used to ensure the uniqueness of the field in the data table and restrict the fields in the data table to not have duplicate data.

A data table can have multiple UNIQUE fields, in addition, NULL values ​​can be accepted in the UNIQUE field.

Suppose we want to restrict the "C_Id" field of the "customer" table to not have duplicate values:

When creating a table, CREATE TABLE...

CREATE TABLE customer (
  C_Id INT NOT NULL UNIQUE,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20)
);

Or write it like this:

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  UNIQUE (C_Id)
);

Name the unique key and the unique limitation of multiple fields

We restrict the two fields of C_Id and Name to be unique together, and the name of the unique key is followed by CONSTRAINT.

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  CONSTRAINT u_Customer_Id UNIQUE (C_Id, Name)
);

Change table limit ALTER TABLE...

ALTER TABLE customer ADD UNIQUE (C_Id);

The only restriction on naming unique keys and multiple fields:

ALTER TABLE customer
ADD CONSTRAINT u_Customer_Id UNIQUE (C_Id, Name);

Remove table restriction ALTER TABLE...

MySQL

ALTER TABLE customer DROP INDEX u_Customer_Id;

SQL Server / Oracle / MS Access

ALTER TABLE customer DROP CONSTRAINT u_Customer_Id;

  • PRIMARY KEY primary key restrictions


PRIMARY KEY primary key constraint (SQL PRIMARY KEY Constraint)

PRIMARY KEY is used to ensure the uniqueness of the field in the data table. Each data in the primary key field must be unique in the data table.

PRIMARY KEY is similar to UNIQUE plus NOT NULL .

There can be only one PRIMARY KEY in a data table, but there can be multiple UNIQUE .

Suppose we want to set the C_Id field in the customer data table as the primary key:

When creating a table, CREATE TABLE...

CREATE TABLE customer (
  C_Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20)
);

Or write it like this:

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  PRIMARY KEY (C_Id)
);

Name the primary key and multi-field key combination (Composite Primary Keys):

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  CONSTRAINT pk_Customer_Id PRIMARY KEY (C_Id, Name)
);

We restrict the two fields of C_Id and Name as primary keys, and the name of the primary key is followed by CONSTRAINT.

When the primary key contains multiple fields, we call it a composite key (Composite Key).

Change table limit ALTER TABLE...

ALTER TABLE customer ADD PRIMARY KEY (C_Id);

Name the primary key and the combination of multiple fields:

ALTER TABLE customer
ADD CONSTRAINT u_Customer_Id PRIMARY KEY (C_Id, Name);

Remove table restriction ALTER TABLE...

MySQL

ALTER TABLE customer DROP PRIMARY KEY;

SQL Server / Oracle / MS Access

ALTER TABLE customer DROP CONSTRAINT pk_PersonID;



  • FOREIGN KEY foreign key restrictions


FOREIGN KEY foreign key constraints (SQL FOREIGN KEY Constraint)

A foreign key is one (or more) fields that point to a primary key in another data table. It restricts the value of the field from only the primary key field of another data table, and is used to determine the referential integrity of the data.

If you want to use foreign key restrictions in a MySQL database, you must use the InnoDB storage engine for the table.

Let us briefly understand what a foreign key is:

This is a customer data table customers

C_IdNameCityAddressPhone
1Zhang YiTaipei City100 XX Road02-12345678
2King TwoHsinchu County200 YY Road03-12345678
3Li SanKaohsiung County300 ZZ Road07-12345678

And this is the data table orders

O_IdOrder_NoC_Id
125723
273753
375201
410542

Here we would like to have a restriction, that is, all customers in the customer order data table must exist in the customers data table. Therefore, we need to set a foreign key in the orders data table, and then point the foreign key to the primary key in the customers data table to ensure that all customers in the orders data table exist in the customers data table, so that there will be no ghost orders Appeared!

FOREIGN KEY Constraint

Suppose we want to set the C_Id field in the orders table as a foreign key and associate it with the C_Id in the customers table:

When creating a table, CREATE TABLE...

CREATE TABLE orders (
  O_Id INT NOT NULL,
  Order_No INT NOT NULL,
  C_Id INT,
  PRIMARY KEY (O_Id),
  FOREIGN KEY (C_Id) REFERENCES customers(C_Id) 
);

Name the foreign key and multi-field foreign key:

CREATE TABLE orders (
  O_Id INT NOT NULL PRIMARY KEY,
  Order_No INT NOT NULL,
  C_Id INT,
  CONSTRAINT fk_Cusomer_Id FOREIGN KEY (C_Id) REFERENCES customers(C_Id) 
);

We restrict C_Id as a foreign key, and CONSTRAINT is followed by the name of the foreign key. Another important point is to remember that C_Id must be set as the primary key in the customers table.

Change table limit ALTER TABLE...

ALTER TABLE orders
ADD FOREIGN KEY (C_Id) REFERENCES customers(C_Id);

Name the foreign key and multi-field foreign key:

ALTER TABLE orders
ADD CONSTRAINT fk_Cusomer_Id FOREIGN KEY (C_Id) REFERENCES customers(C_Id);

Remove table restriction ALTER TABLE...

MySQL

ALTER TABLE orders DROP FOREIGN KEY fk_Cusomer_Id;

SQL Server / Oracle / MS Access

ALTER TABLE orders DROP CONSTRAINT fk_Cusomer_Id;



  • CHECK check limit

CHECK check constraints (SQL CHECK Constraint)

CHECK restrictions are used to constrain the available values ​​in the field to ensure that the data values ​​in the field will meet the conditions you set.

Suppose we want to restrict the value of the C_Id field in the customer data table to be greater than 0:

While creating the table, CREATE TABLE...

CREATE TABLE customer (
  C_Id INT NOT NULL CHECK (C_Id>0),
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20)
);

Or write it like this:

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  CHECK (C_Id>0)
);
There is no error in increasing the CHECK limit in MySQL, but it is useless. CHECK will not be executed!

Name the check limit and check with multiple fields:

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  CONSTRAINT chk_Customer CHECK (C_Id>0 AND Name!='XXX')
);

Change table limit ALTER TABLE...

ALTER TABLE customer ADD CHECK (C_Id>0);

Name the primary key and the combination of multiple fields:

ALTER TABLE customer
ADD CONSTRAINT chk_Customer CHECK (C_Id>0 AND Name!='XXX');

Remove table restriction ALTER TABLE...

ALTER TABLE customer DROP CONSTRAINT chk_Customer;



  • DEFAULT preset value limit


DEFAULT default value limit (SQL DEFAULT Constraint)

The DEFAULT limit is used to set the default value of the field. When you INSERT data, if the field does not specify a value, the default value will be used.

Suppose we want to set the default value of the Address field in the customer table to "unknown":

While creating the table, CREATE TABLE...

CREATE TABLE customer (
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255) DEFAULT 'unknown',
  Phone VARCHAR(20)
);

Change table limit ALTER TABLE...

ALTER TABLE customer ALTER COLUMN Address SET DEFAULT 'unknown';

SQL Server

ALTER TABLE customer ADD DEFAULT 'unknown' FOR Address;

Remove table restriction ALTER TABLE...

ALTER TABLE customer ALTER COLUMN Address DROP DEFAULT;

SQL Server

ALTER TABLE table_name DROP constrain_name;

Oracle

ALTER TABLE table_name MODIFY column_name DEFAULT NULL;



Post a Comment

0 Comments