Introduction to Databases - Assignment Example

Introduction to Databases Introduction to Databases Caution must be taken while dropping primary keys of tables. It is unaccepted to drop a column that has a primary key constraint which is referenced by any foreign key. For example, dropping the primary key employee_id from the employee table would be unacceptable because it is being referenced by foreign key employee_id on the job_history table. Secondly, primary keys cannot be defined on columns that accept null values nor duplicate data. Finally, a single table should only have one primary key at most.

A possible solution is to use CASCADE operation while dropping the primary key. This means the DROP operation will be performed not only on the primary key but also on all referencing foreign keys. Secondly, to avoid having null values as primary keys, always specify the primary key to auto increment whenever the database is populated. One foreign key from a table can only refer to a single primary key on another table. Moreover, it is unacceptable for the same foreign key to refer to multiple primary keys.

For instance, department_id on the employee table refers to the primary key department_id on the department table and not the department_id on the job_history table. The data type and values of foreign key that are used in the database schema must match with the data types and values of primary key or unique columns for that matter. The data values of country_id on countries table, say 11, should be the same as that of country_id on the locations table. Similarly, the data type should also be same.

If region_id in the regions table is an integer, then region_id on the countries table must also be an integer and not a double or float.REFERENCESBressan, S., & Catania, B. (2005). Introduction to database systems. Singapore: McGraw-Hill.Way2DB | Constraints - Way2DB. (n.d.). Way2DB - Way2DB. Retrieved August 19, 2012, from http://www.way2db.in/oracle-constraints.html#PrimaryKeyPrimary & Foreign Key Constraints | Database Solutions for . (n.d.). Retrieved August 19, 2012, from http://www.

databasedev.co.uk/primary_foreign_key_constraints.html