PHP MySQL tutorial

DEFAULT Statement in MySQL

While creating a table in MySQL, you can do a lot more than just specifying the number of columns, their data type and if they hold null values. One option is to specify the default value for a specific column if that column is left empty when the record was inserted. In MySQL, the DEFAULT clause is used for this purpose. The syntax of the DEFAULT clause is as follows:


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

In the above code, the column3 will have default value of 100 if no value is specified for this column when the record was inserted.

Now, lets move towards a more practical example. Let us create a new table named ‘department’, the table will have three columns, dep_id, dep_name and dep_capacity. The third column i.e. dep_capacity will have a default value of 100. Have a look at the following example.




Using DEFAULT statement in MySQL Table



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

$query = "Use Hospital";
$connection->query($query);
    
// Using Default Statement in MySQL
$query = "CREATE TABLE Department (
dep_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
dep_name VARCHAR(50) NOT NULL,
dep_capacity INT(10) DEFAULT 100
);";
if ($connection->query($query) === TRUE) {
    echo "Successful table creation";
} else {
    echo "Unable to create table" . $connection->error;
}

$connection->close();
?>
        




Download the code

If you run the above code, a table named department will be created in the hospital database. Now, if you insert some data into this department table and leave the value for dep_capacity column as empty, you will see that the default value of 100 is assigned to the column. You can verify this by selecting all records via select statement.

<<< INSERT INTO SELECT in MySQLImplementing Check on Table Columns >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .