What is the naming convention for Primary Key column?

Question

Possible Duplicate:
Primary key/foreign Key naming convention

What is the naming convention for Primary Key column in Db Tables?

For instance: PK_Country or CountryId or ID or PrimaryKey or.. ?

Wrong site, go to dba.stackexchange.com. I think it was ask yet. — Spredzy, Jul 14 '11 at 14:45

score 4 · Answer 1 · answered Jul 14 '11 at 14:46

4

I like the Ruby on Rails conventions:

primary key in any table is auto-increment integer column called id
foreign keys are named as foreign table name plus _id. Example:
- country_id is a foreign key that corresponds to a record from the countries table

answered Jul 14 '11 at 14:46

Teddy

18,357
2
30
42

I tend to use this convention too (although I use PHP/Zend Framework), but what about tables with multiple foreign keys to the same table? **Example:** `order` has two columns referencing the same table `user`: who's the user that created the order and who's the user who approved. – Luiz Damim Apr 19 '12 at 13:41
2

You would have to break convention in that case, but I recommend using names that state the obvious, such as `creator_user_id` and `approver_user_id`, or `created_by` and `approved_by`. – Teddy Apr 19 '12 at 14:34
That's what I ended up doing, but I couldn't get to stabilish a convention to this _convention-breaker_ need. :D – Luiz Damim Apr 19 '12 at 16:29
Using primary key with only id is good for active record or data mapping pattern, but it's can make you harder in making query like join. Joining the table with both same `id` you have to point out what `id` field belong to what table. – TomSawyer Jan 23 '17 at 07:53

score 3 · Accepted Answer · edited Apr 19 '14 at 00:33

3

Columns should be named based on the data elements that they represent, not based on what constraints apply to them. A primary key column should be named in the same way you name any other column. The ISO 11179 standard has some useful guidelines for naming data elements.

edited Apr 19 '14 at 00:33

Basil Bourque

303,325
100
852
1,154

answered Jul 14 '11 at 16:20

nvogel

24,981
1
44
82

score 1 · Answer 3 · answered Jul 14 '11 at 14:45

1

If the table is called Test I would call the PK column TestID.

answered Jul 14 '11 at 14:45

Barry Kaye

7,682
6
42
64

1

+1 - I would call it TEST_ID, not every RDBMS supports mixed case well. – Stephanie Page Jul 14 '11 at 18:31

score 0 · Answer 4 · answered Jul 14 '11 at 14:45

0

That is pretty much up to the designer of the db. When I do it I make the field name of the primary key: id and field name of foreign keys: tablenameId

answered Jul 14 '11 at 14:45

John Kane

4,383
1
24
42

score 0 · Answer 5 · answered Jul 14 '11 at 14:45

I always try to start primary keys with PK and foriegn keys with FK. Also, when naming foriegn keys, I try to include the table names and column names in the name of the foriegn key: FK_Questions-test-id_Tests-seq-num would be a foriegn key from table questions to table tests where questinos.test_id = tests.seq_num. i think it helps to name them that way for when you are glancing at your keys.

score 0 · Answer 6 · answered Jul 14 '11 at 14:45

0

There really is not a defined standard; rather, you should develop your own convention and make sure that every primary key you define is consistent with that convention.

answered Jul 14 '11 at 14:45

Brian Driscoll

19,373
3
46
65

What is the naming convention for Primary Key column?

6 Answers6