Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: foreign key naming convention

    I have three tables T1,T2 and T3
    T1 has foreign key with T2 and T3
    what is the better naming convention for declaring foreign key
    .

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: foreign key naming convention

    Originally posted by jaggu
    I have three tables T1,T2 and T3
    T1 has foreign key with T2 and T3
    what is the better naming convention for declaring foreign key
    .
    This is a matter of taste and/or organisation standards. A common naming convention would be like this:

    ALTER TABLE t1 ADD CONSTRAINT t1_t2_fk FOREIGN KEY (t2_id) REFERENCES t2;

    Where table names are long, it is common to define standard abbreviations and then use these in column and constraint names, e.g.

    -- Table EMPLOYEE_ASSIGNMENT: abbreviation EASS
    -- Table PROJECT: abbreviation PROJ

    ALTER TABLE employee_assignment ADD CONSTRAINT eass_proj_fk FOREIGN KEY (proj_id) REFERENCES project;

    The intention is that looking at the name of the constraint it is easy to guess which table it is defined on, and what the constraint does.

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    Hi Tony,
    Thanks a lot.

    Jaggu

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Jaggu,

    As Tony says it is very much a matter of personal taste and/or company policy.

    My personal preference is to group objects using the table name to which they relate. Along the lines of

    tables <tablename>
    foreign keys <tablename><description>_FK_CON
    indexs <tablename><description>_IND
    value constraints <tablename><description>_VAL_CON
    packages <tablename><description>_PCK
    triggers <tablename><description>_TRG

    Business related packages etc which don't relate to any specific table just name as you see fit.

    The benefit is being able to see all objects related to any table fairly quickly, select * from user objects where object_name like 'EMPLOYEE%'

    EMPLOYEE (TABLE)
    EMPLOYEE_INS_TRG (TRIGGER)
    EMPLOYEE_ID_SEQ (SEQUENCE)
    EMPLOYEE_LEAVING_PCK (PACKAGE)
    EMPLOYEE_STARTING_PCK (PACKAGE)
    EMPLOYEE_DEPT_VAL_CON (CONSTRAINT)
    EMPLOYEE_DEPT_FK_CON (CONSTRAINT)

    Etc.

    Hth
    Bill

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    hi Bill,
    This is perferct, I liked it.

    --Jaggu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •