MySQL Forums
Forum List  »  Newbie

Re: How to create sample tables with Data referred in Reference Manual
Posted by: Rachel Gomez
Date: October 11, 2022 01:31AM

There are three main sections in this query:

Create table
This creates the table with all the columns that we want the table to have and the type of information stored. The columns are given a name first, and then a data type that they store. Integer, serial (sequential integer), varchar (a string of word/s).

2. Create unique index

This indicates that the column id that has just been created in the first step can only take unique values. What does this mean? No two patients can have the same identifier. It makes sense to add this constraint to the id of a patient, otherwise people with the same name could get tangled mixed up.

3. Alter table

Finally, we take the same id column, and we assign it the role of Primary Key. We can only assign a column to be the primary key when the values in that column are unique and are in integer form. For our example, ‘patient_pk’ stands for patient primary key.

A SQL table does not need a primary key, but it is a useful feature to keep the data clean and track each entry.

The same process is repeated for the hospital and doctor tables.

create table doctor
(
name varchar,
hospital varchar,
id serial
);


create unique index doctor_id_uindex
on doctor (id);

alter table doctor
add constraint doctor_pk
primary key (id);
Create table to store the hospital information:

create table hospital
(
name varchar,
id serial
);

create unique index hospital_id_uindex
on hospital (id);

alter table hospital
add constraint hospital_pk
primary key (id);
Step 2: Relate the tables through Foreign and Primary Keys
For now, all we have are three tables that exist independent of each other, not knowing the others exist. Let’s set up the relationship between the tables through the addition of foreign keys.

The code for this is as follows:

alter table patient
add constraint patient_doctor_id_fk
foreign key (primary_doctor) references doctor (id);
Lets break this down into three steps:
1. Alter table: Since the table has already been created, we need to alter its current state to add a foreign key.

2. Add a constraint: used to specify rules for the data in a table.This is where we give a name to what we are going to do. In this case, add a foreign key.

3. Foreign key: Here we specify that we are taking the column “primary_doctor” and relating it to the “id” column in the doctor table. This way, when we add a patient with a certain doctor, the tables are immediately linked and we can search for doctor information when we enter a patient and vice-versa.

Similarly to link the doctor table with the hospital table:

alter table doctor

add constraint doctor_hospital_id_fk

foreign key (hospital) references hospital (id);

Regards,
Rachel Gomez

Options: ReplyQuote


Subject
Written By
Posted
Re: How to create sample tables with Data referred in Reference Manual
October 11, 2022 01:31AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.