Monday, 11 October 2021

Case Statement sql videos in telugu 61

 https://youtu.be/7Tredy3PEMA

-----------------------------------------------------
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

CREATE TABLE customers(
    cust_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);


CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(cust_id)
);

INSERT INTO customers (first_name, last_name, email) 
VALUES ('venkat', 'vlr', '[email protected]'),
       ('praveen', 'g', '[email protected]'),
       ('lakshman', 'k', '[email protected]'),
       ('Naveen', 'd', '[email protected]'),
       ('ambani', 'e', '[email protected]');
       
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2021/05/10', 199.99, 300),
       ('2021/09/11', 321.50, 301),
       ('2021/06/12', 789.67, 303),
       ('2021/01/03', 102.50, 304),
       ('2021/04/11', 850.25, 305),
       ('2021/03/13', 689.25, 500),
       ('2021/02/11', 31.50, 301),
       ('2021/06/12', 79.67, 303),
       ('2021/08/03', 102.50, 304),
       ('2021/07/11', 80.25, 305),
       ('2021/12/13', 669.25, 500);

---------


Warning: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe: ignoring option '--no-beep' due to invalid value ''
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use vlrinst;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| dept              |
| emp               |
| faculty           |
| student           |
+-------------------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE customers(
    ->     cust_id INT AUTO_INCREMENT PRIMARY KEY,
    ->     first_name VARCHAR(100),
    ->     last_name VARCHAR(100),
    ->     email VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> insert into customers(cust_id,first_name,last_name,email)
    -> values(300,"venkat","ch","[email protected]");
Query OK, 1 row affected (0.06 sec)

mysql> select * from customers;
+---------+------------+-----------+-----------------------+
| cust_id | first_name | last_name | email                 |
+---------+------------+-----------+-----------------------+
|     300 | venkat     | ch        | [email protected] |
+---------+------------+-----------+-----------------------+
1 row in set (0.00 sec)

mysql> insert into customers(cust_id,first_name,last_name,email)
    -> values(300,"naresh","ch","[email protected]");
ERROR 1062 (23000): Duplicate entry '300' for key 'PRIMARY'
mysql> INSERT INTO customers (first_name, last_name, email)
    -> VALUES ('venkat', 'vlr', '[email protected]'),
    ->        ('praveen', 'g', '[email protected]'),
    ->        ('lakshman', 'k', '[email protected]'),
    ->        ('Naveen', 'd', '[email protected]'),
    ->        ('ambani', 'e', '[email protected]');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
+---------+------------+-----------+------------------------------+
6 rows in set (0.00 sec)

mysql> insert into customers(cust_id,first_name,last_name,email)
    -> values(500,"naresh","ch","[email protected]");
Query OK, 1 row affected (0.07 sec)

mysql> select * from customers;\
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
|     500 | naresh     | ch        | [email protected]        |
+---------+------------+-----------+------------------------------+
7 rows in set (0.00 sec)

mysql> INSERT INTO customers (first_name, last_name, email)
    -> VALUES ('venkat', 'vlr', '[email protected]'),
    ->        ('praveen', 'g', '[email protected]'),
    ->        ('lakshman', 'k', '[email protected]'),
    ->        ('Naveen', 'd', '[email protected]'),
    ->        ('ambani', 'e', '[email protected]');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from customers;\
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | ch        | [email protected]        |
|     301 | venkat     | vlr       | [email protected] |
|     302 | praveen    | g         | [email protected]          |
|     303 | lakshman   | k         | [email protected]         |
|     304 | Naveen     | d         | [email protected]            |
|     305 | ambani     | e         | [email protected]             |
|     500 | naresh     | ch        | [email protected]        |
|     501 | venkat     | vlr       | [email protected] |
|     502 | praveen    | g         | [email protected]          |
|     503 | lakshman   | k         | [email protected]         |
|     504 | Naveen     | d         | [email protected]            |
|     505 | ambani     | e         | [email protected]             |
+---------+------------+-----------+------------------------------+
12 rows in set (0.00 sec)

mysql> CREATE TABLE orders(
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     order_date DATE,
    ->     amount DECIMAL(8,2),
    ->     customer_id INT,
    ->     FOREIGN KEY(customer_id) REFERENCES customers(cust_id)
    -> );
Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)
    -> VALUES ('2021/05/10', 199.99, 1),
    ->        ('2021/04/11', 351.50, 1),
    ->        ('2021/16/12', 789.67, 2),
    ->        ('2021/01/03', 102.50, 2),
    ->        ('2021/04/11', 850.25, 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`vlrinst`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`cust_id`))
mysql>        ('2021/05/13', 689.25, 3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2021/05/13', 689.25, 3)' at line 1
mysql>
mysql> INSERT INTO orders (order_date, amount, customer_id)
    -> VALUES ('2021/05/10', 199.99, 301);
Query OK, 1 row affected (0.06 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  6 | 2021-05-10 | 199.99 |         301 |
+----+------------+--------+-------------+
1 row in set (0.00 sec)

mysql> INSERT INTO orders (order_date, amount, customer_id)
    ->        ('2021/05/13', 689.25, 900);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2021/05/13', 689.25, 900)' at line 2
mysql> INSERT INTO orders (order_date, amount, customer_id)
    -> VALUES ('2021/05/10', 199.99, 7701);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`vlrinst`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`cust_id`))
mysql>

No comments:

Post a Comment

Note: only a member of this blog may post a comment.