List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 15 2005 2:43pm
Subject:Re: Single vs Multiple primary keys
View as plain text  
Bob,

> I have a table, see below, that contains a single primary key
(SubTestCaseKey ) and a number of foreign keys
>
> * plantriggers_ID_FK ,
> * testcase_root_ID_FK
> * testcasesuffix_name_FK
>
> What I want to ensure is that there are no duplicate records when
considering the three foreign keys above. Would it be appropriate to remove
the single primary and replace with three multiple primary keys? Is there a
performance impact when doing this. this seems overly complex and wonder if
I should be breaking the table up to simplify? Any suggestions would be
appreciated.
>

There's no such thing as 3 primary keys.

Why do you need "subtestcasekey"? If the foreign key columns should be
unique, why not
make those 3 columns the primary key?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

> Bob
>
> CREATE TABLE testplans (
>   SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
>   plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
>   testcase_root_ID_FK INTEGER NOT NULL,
>   testcasesuffix_name_FK VARCHAR(20) NULL,
>   FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
>   Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
>   DateMod TIMESTAMP NULL,
>   tester_list_Name_FK VARCHAR(50) NULL,
>   PRIMARY KEY(SubTestCaseKey),
>   INDEX testplans_FKIndex1(tester_list_Name_FK),
>   INDEX testplans_FKIndex2(testcasesuffix_name_FK),
>   INDEX testplans_FKIndex3(testcase_root_ID_FK),
>   INDEX testplans_FKIndex4(plantriggers_ID_FK),
>   FOREIGN KEY(tester_list_Name_FK)
>     REFERENCES tester_list(Name)
>       ON DELETE RESTRICT
>       ON UPDATE CASCADE,
>   FOREIGN KEY(testcasesuffix_name_FK)
>     REFERENCES testcasesuffix(name)
>       ON DELETE RESTRICT
>       ON UPDATE CASCADE,
>   FOREIGN KEY(testcase_root_ID_FK)
>     REFERENCES testcase_root(ID)
>       ON DELETE RESTRICT
>       ON UPDATE CASCADE,
>   FOREIGN KEY(plantriggers_ID_FK)
>     REFERENCES plantriggers(ID)
>       ON DELETE RESTRICT
>       ON UPDATE CASCADE
> )
> TYPE=InnoDB;

Thread
Single vs Multiple primary keysBob)15 May
  • Re: Single vs Multiple primary keysMartijn Tonies15 May
    • Re: Single vs Multiple primary keysDan Bolser15 May
      • Re: Single vs Multiple primary keysDaniel Walker16 May