CODEWITHSUNDEEP
mysql
jimmy yorke
jimmy yorke

Reputation: 5

MySQL Foreign key and reference

How can I create a table with multiple foreign keys using references. For example, I created a table called SCHOOL. In the SCHOOL TABLE, I created columns called, STUDENTS, TEACHERS, BOOKS, COURSES, ADVISOR. All these columns are foreign keys. Can you come up with an example showing how you can create foreign keys using references?

Upvotes: 0

Views: 114

Answers (1)

ducin
ducin

Reputation: 26487

You're making fundamental mistakes on understanding relational databases. Each object in the world should be an entity. A school is an entity, a student is an entity, a teacher is an entity and so on. Each of them should have their own table.

School table should have columns like: id (int, primary key), name (varchar), etc. Teachers, books and students could reference school by a 1:n relation. This means that a teacher is bound to a school, technically: each of them has a school_id (int) which is a foreign key column. In short this means, that if your school table has a record: id=4,name=MyHighschool and you've got a teacher record with id=5,school_id=4 this means that this teacher references that school. This is how relations work in RDBMS.

But this is a very very basic example. I'd recommend you to read some beginner SQL relations tutorial just to get an understanding of what is a table and what are the relations (1:1, 1:n, m:n) - this is obligatory to use databases.

Upvotes: 1

Related Questions