Translate

Monday 18 October 2021

SQL FOREIGN KEY on ALTER TABLE v to existing table ,alter table sql videos in telugu 103

https://youtu.be/b5tkbbnQ_6g 

---------------------------------------------------------------
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:


ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);



ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
---------------
create table book (id int primary key auto_increment, name varchar(50), library int);
create table library(id int auto_increment primary key, name varchar(50));
insert into library (id,name) values (10,'java')

insert into book (name, library) values ('mysql', 10)

alter table book add constraint fk_book_library foreign key (library) references library(id)

alter table book drop foreign key fk_book_library



insert into library (name) values ('java'), ('dotnet');

insert into book (name, library) values ('mysql', 10)

alter table book add constraint fk_book_library foreign key (library) references library(id)

alter table book drop foreign key fk_book_library;

Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.25 MySQL Community Server - GPL

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

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 classicmodels;
Database changed
mysql> create table book (id int primary key auto_increment, name varchar(50), library int);create table library(id int auto_increment primary key, name varchar(50));insert into library (name) values ('java'), ('dotnet');insert into book (name, library) values ('mysql', 10)alter table book add constraint fk_book_library foreign key (library) references library(id)alter table book drop foreign key fk_book_library;
Query OK, 0 rows affected (0.91 sec)

Query OK, 0 rows affected (1.05 sec)

Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

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 'alter table book add constraint fk_book_library foreign key (library) references' at line 1
mysql> create table book (id int primary key auto_increment, name varchar(50), library int);
ERROR 1050 (42S01): Table 'book' already exists
mysql> create table library(id int auto_increment primary key, name varchar(50));
ERROR 1050 (42S01): Table 'library' already exists
mysql> desc book;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | YES  |     | NULL    |                |
| library | int         | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc library
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into library (name) values ('java'), ('dotnet');
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from library;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | dotnet |
|  3 | java   |
|  4 | dotnet |
+----+--------+
4 rows in set (0.00 sec)

mysql> delete from library;
Query OK, 4 rows affected (0.15 sec)

mysql> insert into library (name) values ('java'), ('dotnet');
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from library;
+----+--------+
| id | name   |
+----+--------+
|  5 | java   |
|  6 | dotnet |
+----+--------+
2 rows in set (0.00 sec)

mysql> alter table book add constraint fk_book_library foreign key (library) references library(id);
Query OK, 0 rows affected (3.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into book (name, library) values ('mysql', 10)
    -> ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`classicmodels`.`book`, CONSTRAINT `fk_book_library` FOREIGN KEY (`library`) REFERENCES `library` (`id`))
mysql> insert into library (id,name) values (10,'java')
    -> ;
Query OK, 1 row affected (0.19 sec)

mysql> insert into book (name, library) values ('mysql', 10);
Query OK, 1 row affected (0.10 sec)

mysql> alter table book drop  fk_book_library;
ERROR 1091 (42000): Can't DROP 'fk_book_library'; check that column/key exists
mysql> alter table book drop foreign key  fk_book_library;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

No comments:

Post a Comment

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