Thanlks for the reply Gregg. How would an IOT table help me? Is it more efficient than having an index only table? The reason I have a PK here is that I intend to use these exact same fields as a FK in another table.
Ok here is the example of the problem I have. There is an EMP table (fields employee code, employee name) and DEPT table (field dept code, dept name). An employee can be associated with many departments. Then there are attributes like the date when an employee is assigned to a department etc. I create an EMP-DEPT table to hold the transaction details between the Employee and the Department (fields employee code (FK), dept code(FK)) which tells me which employee belongs to which departments. I make both the fields the primary key of that table. My first question is: Is it allowed to have a table where all fields are the primary key? I then create another table EMP-DEPT-DTLS which describes other attributes of the EMP-DEPT relationship like when the employee joined the department etc. So what I have is now two tables: one EMP-DEPT that holds the association between the EMP and the DEPT (where all fields are part of the primary key), and another EMP-DEPT-DTLS that holds attribute details of this relationship (the primary key of the EMP-DEPT-DTLS is the same as the primary key of the EMP-DEPT table). The reason I would not like to merge the two tables which have similar keys is that while the EMP-DEPT relationship may be valid for a month, the EMP-DEPT-DTLS attributes may change many times a day. Is this good design? Any other ideas?
1. A table's entire column structure can be a primary key.
2. An IOT table looks, smells, and feels like a table, but it is actually only an index. The query is quicker since you don't have to read the rowid from the index and take it over to query the table. For maintenance (DML), there is only 1 object to update, insert, delete ... the index.
3. The design you are looking at depends on your needs ... If you need 3 tables to accomplish what you are trying to do, then fine ...
Yes, it is allowed to have all columns in the primary key. And every table should have a primary key (no exceptions!)
But I don't understand what you are trying to do with the EMP_DEPT_DTLS table having the same PK as EMP_DEPT. If the 2 tables have the same PK they may as well be one table. Perhaps you really meant that the PK of EMP_DEPT_DTLS is the combination of the FK to EMP_DEPT and another column, e.g. start_date? In other words, a 1:many relationship not a 1:1?
A more typical model would be:
CREATE TABLE emp_dept
( emp_id REFERENCES emp
, dept_id REFERENCES dept
, start_date DATE
, end_date DATE
, ... other details
, PRIMARY KEY (emp_id, dept_id, start_date )
This allows emp_dept to hold many assignments of the same employee to the same department, but with different start dates.
Guys thank you for your time and effort. And yes, I will add the start date to the PK of the EMP-DEPT-DTLS table so that the tables have PKs with different combinations of columns. That was one of my worries - whether having two tables with the same columns for a PK indicated bad modelling.
It is not always bad modelling to have two tables with the same PK. This may be done for subtyping, for example. But when there will always be exactly the same number of records in both tables, then usually a single table is preferable. There are always exceptions, of course.