Posted By
Bajarangi soft ,
Posted On 18-09-2020
SQL FOREIGN KEY.
Using SQL CREATE TABLE Statement
1.create a table called "Categories" in the management database that contains different datatype columns:
CREATE TABLE Categories(
cat_id int not null auto_increment primary key,
Name varchar(255) not null,
Description text
) ;
2.Create a table called "Products" in the management database that contains different datatype columns:
CREATE TABLE Products(
ProductID int not null auto_increment primary key,
ProductName varchar(355) not null,
ProductPrice decimal,
cat_id int not null,
FOREIGN KEY fk_cat(cat_id)
REFERENCES categories(cat_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
The "cat_id" column in the "Categories" table is the PRIMARY KEY (Main table).The "cat_id " column in the "Products" table is a FOREIGN KEY (Sub table).Now Products table referring the Categories table so that user can also access both table
Use DESC command that display the "Categories" and "Products " table.
2.Complete code of SQL FOREIGN KEY in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to use SQL FOREIGN KEY </title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<br>
<br>
<div class="text-center">
<h1>SQL FOREIGN KEY</h1>
</div>
<br>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "management");
if($connect === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql_1 = "CREATE TABLE Categories(
cat_id int not null auto_increment primary key,
Name varchar(255) not null,
Description text
) ";
$sql_2 = "CREATE TABLE Products(
ProductID int not null auto_increment primary key,
ProductName varchar(355) not null,
ProductPrice decimal,
cat_id int not null,
FOREIGN KEY fk_cat(cat_id)
REFERENCES categories(cat_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);";
if(mysqli_query($connect, $sql_1)){
mysqli_query($connect,$sql_2);
echo "<br><h1 class='text-success'>Categories and Products tables created successfully.</h1>";
} else{
echo "ERROR: Could not able to execute $sql_1. " . mysqli_error($connect);
}
mysqli_close($connect);
?>
</div>
<br>
</div>
</body>
</html>