Some DB FAQs

A. About composite primary key

B. About data insertion: Different ways to work the INSERT INTO clause

C. About order of table creation : Existence of tables are importance when apply a foreign key constraint

 
 
 
 

 

A. About composite primary key

Why do we need a composite primary key?

  • A composite key is a key having more than one attribute.
  • In some case, we may need more than one attribute to pinpoint one unique row.
    • For example, when ordering a meal in a restaurant, we many order a combination of foods (also for beverage). Therefore, those slice of information will appear in an ORDERDETAIL table. (Please see the blow table definition)
    • A composite primary key must be defined separately at one place --> primary key (attr_1, attr_2, ..., attr_n). 
    • Note: Foreign key constraints are crossed out for now. Will be explained in "order of table to be created" section )

Table definition-

create table Orderdetail
(
Order_ID varchar(5) not null,
Food_Bev_ID varchar(5) not null,
Order_DateTime timestamp not null,
primary key (Order_ID, Food_Bev_ID),
constraint foreign key (Order_ID) references Order (O_ID),
constraint foreign key (Food_Bev_ID) references FoodBev (F_B_ID)

);

  • Order_ID and Food_Bev_ID together become a composite primary key for this table. As a result, row 1 and 2 are different even if they belong to order# C23.
    • Order_ID C23 and Food_Bev_ID B77
    • Order_ID C23 and Food_Bev_ID B90 
  • Traditionally, a field selected to be a primary key must be unique (no duplication). But in our case, Order_ID must have duplicate values, as well as for Food_Bev_ID.
  • Fortunately, a composite primary key allows each key attribute to have duplicate values. (C23, C23, ...). Therefore, we use a composite primary key in this case.
-----

Another example, SEATING (book_id, cinema_id, showtime_id, row, seat, status)

  • Table “SEATING” contains a composite PK (Primary Key). Well, you may think this way:
  • When you goes see the movie, in order to know your “row” (and/or “seat”  and/or “status”) just for a specific visit, a cinema officer needs to know your “book_id”, “cinema_id”, and “showtime_id”. Right?
  • Knowing only your “book_id” cannot tell your “row” (and/or “seat”  and/or “seat_status_code”). ---because we don’t know which cinema you will go to (cinema_id) and when will you watch the movie (showtime_id).
  • Similarly, knowing only your “cinema_id” cannot tell your “row”… and so on. Knowing only your “showtime_id” cannot tell your “row”… and so on.

That’s why we need to know all three keys ({book_id, cinema_id, and showtime_id} = composite key) to exactly tell your seating.

  

B. About data insertion: Different ways to work the INSERT INTO clause

There are many ways There many ways to work the INSERT INTO clause.

For example, if you would like to insert 2 rows of data into a table having 3 fields:

Table definition-

create table table_A (
col_1 varchar(10),
col_2 varchar(10),
col_3 varchar(10)
);

  • Use separate INSERT INTO clause for each row.
    • INSERT INTO table_A (col_1, col_2,col_3) VALUES ('V_1','V_2','V_3'); INSERT INTO table_A (col_1, col_2,col_3) values ('V_4','V_5','V_6');
  • Use a single INSERT INTO clause.
    • INSERT INTO table_A (col_1, col_2,col_3) VALUES ('V_1','V_2','V_3'), ('V_4','V_5','V_6');
  • Insertion a data sets having null values.
    • INSERT INTO table_A (col_1, col_2,col_3) VALUES ('V_1','V_2', null), ('V_4', null, 'V_6');
  • Insert data into some fields only.
    • INSERT INTO table_A (col_1, col_3) VALUES ('V_1', null), ('V_4', 'V_6');
  • Insert data into all fields.
    • INSERT INTO table_A VALUES ('V_1','V_2','V_3'), ('V_4','V_5','V_6');
  

 

C. About order of table creation : Existence of tables are importance when apply a foreign key constraint.

Table definition-

create table Orderdetail
(
Order_ID varchar(5) not null,
Food_Bev_ID varchar(5) not null,
Order_DateTime timestamp not null,
primary key (Order_ID, Food_Bev_ID),
constraint foreign key (Order_ID) references Order
 (O_ID),
constraint foreign key (Food_Bev_ID) references
FoodBev (F_B_ID)
);
  • Because the the table 'orderdetail' will refer to tables 'Order' and 'FoodBev', therefore, tables 'Order' (with 'O_ID' field) and 'FoodBev' (with 'O_ID' field) must be created prior to specifying the foreign key constraints in the table 'orderdetail'

 

D. About naming convention

  • Please keep in mind that there are reserved word that we should avoid using them. (Actually you can use those words by quote them, yet, not recommended)
  • List of reserved words.
 

E. MySQL Data Types summary (Links)

 

 

...Dr.Thadthong Bhrammanee