-2

This may seem like a simple question, but I am stumped:

I have created a database about cars (in Oracle SQL developer). I have amongst other tables a table called: Manufacturer and a table called Parentcompany. Since some manufacturers are owned by bigger corporations, I will also show them in my database. The parentcompany table is the "parent table" and the Manufacturer table the "child table". for both I have created columns, each having their own Primary Key.

For some reason, when I inserted the values for my columns, I was able to use the same value for the primary key of Manufacturer and Parentcompany

The column: ManufacturerID is primary Key of Manufacturer. The value for this is: 'MBE'

The column: ParentcompanyID is primary key of Parentcompany. The value for this is 'MBE'

Both have the same value. Do I have a problem with the thinking logic? Or do I just not understand how primary keys work? Does a primary key only need to be unique in a table, and not the database?

I would appreciate it if someone shed light on the situation.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Donovan
  • 1

4 Answers4

1

A primary key is unique for each table.

Have a look at this tutorial: SQL - Primary key

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

McNets
  • 10,352
  • 3
  • 32
  • 61
0

Primary key is table-unique. You can use same value of PI for every separate table in DB. Actually that often happens as PI often incremental number representing ID of a row: 1,2,3,4...

For your case more common implementation would be to have hierarchical table called Company, which would have fields: company_name and parent_company_name. In case company has a parent, in field parent_company_name it would have some value from field company_name.

Alex
  • 731
  • 1
  • 6
  • 21
  • I wouldn't ever use company name as a primary key. There can be at least one, and potentially many (depending on laws), identical in every jurisdiction. There will be many that are nearly identical. – Ben Feb 25 '18 at 13:45
  • As I mentioned above, PI is usually generated IDs (1,2,3,4...). Using names was provided as illustration of idea of hierarchy. But you are right, names are not commonly used as PI, yet not wrong in some cases. – Alex Feb 25 '18 at 14:05
0

There are several reasons why the same value in two different PKs might work out with no problems. In your case, it seems to flow naturally from the semantics of the data.

A row in the Manufacturers table and a row in the ParentCompany table both appear to refer to the same thing, namely a company. In that case, giving a company the same id in both tables is not only possible, but actually useful. It represents a 1 to 1 correspondence between manufacturers and parent companies without adding extra columns to serve as FKs.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
0

Thanks for the quick answers! I think I know what to do now. I will create a general company table, in which all companies will be stored. Then I will create, as I go along specific company tables like Manufacturer and parent company that reference a certain company in the company table. To clarify, the only column I would put into the sub-company tables is a column with a foreign key referencing a column of the company table, yes?

For the primary key, I was just confused, because I hear so much about the key needing to be unique, and can't have the same value as another. So then this condition only goes for tables, not the whole database. Thanks for the clarification!

Donovan
  • 1
  • Your issue looks like old example about "employees" and their "managers" that are "employees" too. Read, please: https://docs.oracle.com/cd/B28359_01/server.111/b28318/data_int.htm#BABHGIJJ For such cases, its better to use one table that have primary key (COMPANY_ID) and PARENT_ID column that references on primary key of the same table. Thus, you would use only one table and one foreign key. Here is the question with examples: https://stackoverflow.com/questions/8768118/enforce-a-foreign-key-constraint-to-columns-of-same-table – Dima Yankin Feb 25 '18 at 21:53