How to use SQL CHECK constraint &case when

1.CHECK constrained SQL CHECK constraints

A CHECK constraint is used to limit the range of values in a column.

If you define a CHECK constraint on a single column, the column only allows a specific value.

If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint for the "id_p" column when the "Persons" table is created. The CHECK constraint specifies that the "id_p" column must contain only integers greater than 0.

My SQL:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CHECK (Id_P>0) )

SQL Server/oracle/ms Access:

CHECK (Id_P>0), LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

If you need to name a check constraint and define a CHECK constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes‘) )

SQL CHECK Constraint on ALTER TABLE

If you create a CHECK constraint for the "id_p" column if the table already exists, use the following SQL:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CHECK (Id_P>0)

If you need to name a check constraint and define a CHECK constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes‘)

Revoke a CHECK constraint

To revoke a check constraint, use the following SQL:

SQL Server/oracle/ms Access:

ALTER TABLE PersonsDROP CONSTRAINT chk_Person

Mysql:

ALTER TABLE PersonsDROP CHECK chk_Person

2. Case when (http://www.cnblogs.com/yazdao/archive/2009/12/09/1620482.html)

Case has two formats. Simple case function and case search function.

--Simple case function Sexwhen ' 1 ' then ' Male ' when ' 2 ' then ' women ' else ' other ' end--case search function case when sex = ' 1 ' Then ' man ' when sex = ' 2 ' Then ' Women ' Else ' other ' END

In both of these ways, the same functionality can be achieved. The simple case function is relatively concise, but there are some limitations in function, such as write-judgement, compared to the search function.
There is also a problem to be aware that the case function returns only the first qualifying value, and the remaining case section is automatically ignored.

--for example, the following SQL, you can never get the result of a "second class" When the case is col_1 in (' A ', ' B ') then ' first Class ' When col_1 in (' a ') then       ' second ' else ' other ' END

Let's take a look at what you can do with the case function.

One, the known data in a different way to group, analysis.

There is the following data: (in order to see more clearly, I did not use the country code, but directly with the country name as primary Key)

Country (country)	Population (population)
China	600
United States	100
Canada	100
United Kingdom	200
France	300
Japan	250
Germany	200
Mexico	50
India	250

According to the population data of this country, the population of Asia and North America is counted. The following result should be obtained.

Chau	Population
Asia	1100
North america	250
Other	700

What would you do to solve this problem? Creating a view with a continent code is a workaround, but it is difficult to dynamically change the way statistics are used.
If you use the case function, the SQL code is as follows:

SELECT  SUM (population), case Countrywhen ' China ' then ' Asia ' when ' India ' then ' Asia ' when '     Japan ' then     ' Asia ' when ' US ' C5/>then ' North America ' when ' Canada ' then ' North America ' when  ' Mexico ' then  ' North America ' Else ' other ' endfrom table_agroup by case    Countrywhen ' China ' Then ' Asia ' when ' India ' then ' Asia ' when ' Japan ' then ' Asia ' when ' us ' then ' North America ' when     ' Canada ' then  ' North America ' when ' Mexico ' then  ' North America ' Else ' other ' END;

Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows;

Selectcase When salary <= 1 "When salary > Salary <= and  " 2 "when salary > + SA Lary <=  3 ' When salary > Salary <= and ' 4 ' ELSE NULL END salary_class,count (*)    from Table_agroup Bycase When salary <= "1 ' When salary > Salary <= and  " 2 "when salary > 6 XX and Salary <=  3 ' When salary > Salary <= and ' 4 ' ELSE NULL END;

Two, a SQL statement is used to complete the grouping of different conditions.

Have the following data

Country (country)	Gender (sex)	Population (population)
China	1	340
China	2	260
US	1	45
US	2	55
Canada	1	51
Canada	2	49
UK	1	40
UK	2	60

Grouped according to country and gender, the results are as follows

Countries	Man	Woman
China	340	260
United States	45	55
Canada	51	49
United Kingdom	40	60

In general, a Union can also be used to implement a query with a single statement. But that increases the consumption (two select parts), and the SQL statement is longer.
Here is an example of using the case function to accomplish this function

SELECT country,sum (case when sex = ' 1 ' thenpopulation else 0 END),  --Male population SUM (case when sex = ' 2 ' thenpopulation else 0 END)   --female population from  table_agroup by country;

In this way, we use Select to complete the output form of the two-dimensional table, which fully shows the strong case function.

third, use the case function in check.

Using the case function in check is a very good workaround in many cases. There may be a lot of people who don't have check at all, so I suggest you try using check in SQL after reading the following example.
Let's give an example.
Company A, the company has a rule that female employees must pay more than 1000 yuan. If you use check and case to behave as follows

CONSTRAINT check_salary Check (case if sex = ' 2 ' then case when salary > 1000THEN 1 ELSE 0 endelse 1 END = 1)

If you simply use check, as shown below

CONSTRAINT check_salary Check (sex = ' 2 ' and salary > 1000)

The condition of the female clerk was met, and the male clerk could not enter it.

Me summary: Check constraint is very simple, I believe you can see it. Case-when is equivalent to the Switch-case statement in the program, as long as you understand the principle of the switch case statement, Case-when should be a cinch. Case-when is widely used, but it is important to note that the Case-when statement block needs to be duplicated in the group-by statement.

How to use SQL CHECK constraint &case when