Creating a database and then adding to it a foreign key

Assignment Help Database Management System
Reference no: EM13842215

Following is an example of creating a database and then adding to it a foreign key constraint

mysql> create database dbdemo;

Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE vendors(

-> vdr_id int not null auto_increment primary key,

-> vdr_name varchar(255)

-> )ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

CREATE TABLE categories(

-> cat_id int not null auto_increment primary key,

-> cat_name varchar(255) not null,

-> cat_description text

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE products(

-> prd_id int not null auto_increment primary key,

-> prd_name varchar(355) not null,

-> prd_price decimal,

-> cat_id int not null,

-> FOREIGN KEY fk_cat(cat_id)

-> REFERENCES categories(cat_id)

-> ON UPDATE CASCADE

-> ON DELETE RESTRICT

-> )ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE products

-> ADD COLUMN vdr_id int not null AFTER cat_id;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE products

-> ADD FOREIGN KEY fk_vendor(vdr_id)

-> REFERENCES vendors(vdr_id)

-> ON DELETE NO ACTION

-> ON UPDATE CASCADE;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe City;

+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| CityName | varchar(30) | YES | | NULL | |
| From_There | varchar(20) | NO | PRI | | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from City;
+--------------------+-------------------+
| CityName | From_There |
+--------------------+-------------------+
| Madrid , Spain | Mike JR |
| Sydney, Australia | Jill Engelstein |
| San Francisco, USA | John Smith |
| Hyderabad, India | Sudhir Srinivasan |
+--------------------+-------------------+

4 rows in set (0.00 sec)

mysql> describe people;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| email | varchar(20) | NO | PRI | | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from people;
+-------------------+-------------------+
| name | email |
+-------------------+-------------------+
| Jill Engelstein | [email protected] |
| John Smith | [email protected] |
| Mike JR | [email protected] |
| Sudhir Srinivasan | [email protected] |
+-------------------+-------------------+

4 rows in set (0.00 sec)

I am trying to create a foreign key:

mysql> alter table people

-> add foreign key fk_name(name)

-> references City(From_There)

-> on delete no action

-> on update cascade;

ERROR 1215 (HY000): Cannot add foreign key constraint

What is wrong?

Reference no: EM13842215

Questions Cloud

Complete the statement of owners equity : Important complete the Income Statement first, complete the Statement of Owners' Equity second, complete the Balance Sheet third, and complete the Statement of Cash Flows last.
Writing a request for proposal on sales promotion : Writing a Request for Proposal on Sales Promotion. Describe the extent of products and services your organization is looking for, as well as, the overall objectives of the contract
Where in the organism is the cell found? : Where in the organism is the cell found?
Describe the purposes of coding diseases and operations : List and explain five characteristics of useful clinical terminologies. Describe the purposes of coding diseases and operations.
Creating a database and then adding to it a foreign key : creating a database and then adding to it a foreign key constraint
Differences between economic and accounting concepts of cost : What are the differences between economic and accounting concepts of cost. How are prices determined under perfect competition
What is the wavelength of neutrons traveling : What is the wavelength of neutrons traveling
Lisp programming and prolog : You have to do all the assignment need its lisp programming and prolog also, the question 2 do it from Microsoft word please i want perfect assignment also, please no plagiarism and no website its very important to me
Business to start with little capital : Could you tap a skill you already possess. For example, could you be a foreign language tutor Compile a list of all the business opportunities you have discovered and share them with your classmates. What are they willing to pay for your products ..

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd