Let us consider the same example of library database to see how records will be inserted and selected from tables.
All the tables in the library database are now with constraints and mappings. Hence we cannot simply insert the records into them. We need to insert the records into the parent tables first, and then to child tables. This is because of the referential integrity. That means, if any table contains the foreign key, then the table from which foreign key is derived should be loaded first. For example, among AUTHOR and BOOK_AUTHOR tables, AUTHOR is the parent table and BOOK_AUTHOR is the child table – because BOOK_AUTHOR has the columns derived from AUTHOR table. Hence we need to insert data into AUTHOR table first.
Let us first list the parent and child tables like below.
We need to fill parent tables first using insert scripts. Let us start with AUTHOR.
INSERT INTO AUTHOR VALUES (‘GVP_012’, ‘GERALD V POST’, ‘PO BOX No 12, Delhi’);
This insert statement inserts the record into AUTHOR table. Here we have not listed the order of the columns in the table. The values in the insert statements are now inserted into first, second and third columns respectively. If we need to change the order of columns or values, then we need to specify the column list in the insert statement like below :
INSERT INTO AUTHOR (AUTHOR_ID, ADDRESS, AUTHOR_NAME)
VALUES ('GVP_012', 'PO BOX No 12, Delhi', 'GERALD V POST');
Now that order of the column is different from that appears in the table, we have specified the column list in the insert statement and values for them are inserted in the same order as column list.
Let us insert next parent table record – PUBLISHER, CATEGORY, BORROWER. Since these tables are parent table, we can go on inserting the records without any checks. Only care should be taken to satisfy the datatypes and column size.
INSERT INTO PUBLISHER VALUES (‘TTMH_2324’, ‘Tata McGraw Hill’,’Delhi’);
INSERT INTO CATEGORY VALUES (‘DBMS_1000’, ‘Database’);
INSERT INTO BORROWER VALUES (‘RBT_1000’, ‘Robert’, ‘PO BOX No 34 Delhi’, 9880988067);
Screenshots show how the records appear after they are inserted into respective tables. Now all the parent tables are inserted with data. Let us start inserting child table data from BOOKS table.
INSERT INTO BOOK VALUES (‘70608451’, ‘Database Management Systems’, ‘TTMH_2324’, 800);
Here again column lists are not specified, but values are inserted in the order they appear in the table. PUBLISHER_ID is the foreign key column. Hence the value for this column is obtained from already inserted value in PUBLISHER table. If we try to insert some other value other than the one in PUBLISHER table, then it will give integrity error like below. It does not says foreign key or referential integrity error while inserting but shows like below. But it is the issue of giving wrong value for foreign key column.
Let us insert records into other child tablesv:
INSERT INTO BOOK_AUTHOR VALUES (‘GVP_012’, ‘70608451’);
INSERT INTO BOOK_CATEGORY VALUES (‘70608451’, ‘DBMS_1000’);
INSERT INTO BOOK_COPY VALUES (‘70608451’, ‘1’, TO_DATE (‘1-Jan-2014’, ‘DD-MON-YY’));
INSERT INTO BOOK_LOAN VALUES (‘RBT_1000’, ‘70608451’, 2, TO_DATE (‘1-May-15’, ‘DD-MON-YY’), TO_DATE (’11-May-15′, ‘DD-MON-YY’));
Now all the tables are inserted with one record each and all these records in the table are related to one another. Similarly, let us insert few more records into all the tables.