Composed primary key or excess integer(number) primary key

Asked Mar 10 '15 at 14:04

Active Mar 10 '15 at 14:07

Viewed 27 times

I need an advice in table creation. What is the best? if you can explain why, it would be grateful.

CREATE TABLE TRANSLATION (
       LANGUAGE_CODE CHAR(2) NOT NULL,
       TRANSLATION VARCHAR2(30) NOT NULL,
       OBJECT_ID NUMBER NOT NULL,
       OBJECT_TYPE VARCHAR(10) NOT NULL,
       CONSTRAINT P$TR#PRIMARY PRIMARY KEY (LANGUAGE_CODE, OBJECT_ID, OBJECT_TYPE)
)

CREATE TABLE TRANSLATION (
       ID NUMBER NOT NULL,
       LANGUAGE_CODE CHAR(2) NOT NULL,
       TRANSLATION VARCHAR2(30) NOT NULL,
       OBJECT_ID NUMBER NOT NULL,
       OBJECT_TYPE VARCHAR(10) NOT NULL,
       CONSTRAINT P$TR#PRIMARY PRIMARY KEY (ID),
       CONSTRAINT P$TR#PRIMARY UNIQUE (LANGUAGE_CODE, OBJECT_ID, OBJECT_TYPE)
)

As you can see the difference only in existing primary key in the second example. I will never have to find such rows by id because no other table have references to it. I use Oracle db

asked Mar 10 '15 at 14:04

Oleksandr Samsonov

1,067
2
14
29

1

In my work, the policy is to *always* use an explicit, integer ID field for primary key. – StilesCrisis Mar 10 '15 at 14:10
1

Definitely Option 1 - why do you need surrogate key? No benefits from it. Values from sequences are not transportable across environments. – Rusty Mar 10 '15 at 14:11
@StilesCrisis thanks for reference. I am not noticed it. – Oleksandr Samsonov Mar 10 '15 at 14:13

Composed primary key or excess integer(number) primary key

0 Answers0