PHP MySQL tutorial

Implementing Check on Table Columns

In the last article, we saw how we can specify default value for a column and in case if that column is left empty while data insertion, the default value was entered for that column. In this article, we shall see how we can implement checks on specific columns of a table.

A check is basically a limit on the values that can be stored in the column. For instance you can specify that a specific column can only contain a value between 100 and 200 hundred etc. The syntax of CHECK statement is as follows:


CREATE TABLE table_name
(
column1 int NOT NULL,
column2 varchar(255) NOT NULL,
column3 int DEFAULT 100
CHECK (column1>0)
)

The above code is imposing a check in column1 of the table by specifying that it can only contain a value greater than 0. Let’s see a more real world example. We shall create a patient table with values patient_id, patient_fname, patient_lname, patient_age and dep_id. We shall implement a check on patient_age column that it should contain a value greater than 0. Have a look at the following example.




Using MySQLi for Check Implementation on columns



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 

$query = "Use Hospital";
$connection->query($query);
    
// Implementing Check on Columns
$query = "CREATE TABLE Patient (
patient_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
patient_fname VARCHAR(50) NOT NULL,
patient_lname VARCHAR(50) NOT NULL,
patient_age INT(10),
dep_id INT(10),
CHECK (patient_age > 0)
);";
if ($connection->query($query) === TRUE) {
    echo "Successful table creation";
} else {
    echo "Unable to create table" . $connection->error;
}

$connection->close();
?>
    



Download the code

<<< DEFAULT Statement in MySQLINNER JOIN in MySQL >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .