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.