locked
Primary Key, foreign keys RRS feed

  • Question

  • User-750935311 posted

    I have a table table that is comprised of 4 foreign keys - and nothing else.  I remember a teacher once telling me that in this case I should make all 4 foreign keys as a primary key - or something like that.  I am using MS SQL Server 2012 it which restricts me to one foreign key only.  So what should I do, create a useless field and make that primary?  The load is very light.  This table will probably only be called 1,000 times per week if it grows big.

    This table handles enrolement into classes offered by Aikido dojos:

    FK_DojoID

    FK_FamilyID

    FK_MemberID

    FK_ClassID

    That is all I need.  Seems silly to have an EnrolementID and make that primary.

    What do you suggest?

    Sunday, January 12, 2014 4:01 PM

Answers

  • User-595703101 posted

    Hi Tualatin,

    Your sentence about SQL Server 2012, that is restricts you to use one foreign key only is false.

    You can define different foreign keys in a table. Please check it again you are possible doing something wrong if you are using SQL Server Management Studio

    If you are using SQL DDL for creating your table, you can use one similar as shown below

    create table Dojos(
    	DojoID int
    	constraint PK_DojoID PRIMARY KEY (DojoID)
    )
    create table Family(
    	FamilyID int
    	constraint PK_FamilyID PRIMARY KEY (FamilyID)
    )
    create table Members(
    	MemberID int
    	constraint PK_MemberID PRIMARY KEY (MemberID)
    )
    create table Class(
    	ClassID int
    	constraint PK_ClassID PRIMARY KEY (ClassID)
    )
    
    create table AikidoDojos (
    	DojoID int
    	constraint FK_DojoID FOREIGN KEY REFERENCES Dojos (DojoID),
    
    	FamilyID int
    	constraint FK_FamilyID FOREIGN KEY REFERENCES Family (FamilyID),
    
    	MemberID int
    	constraint FK_MemberID FOREIGN KEY REFERENCES Members (MemberID),
    
    	ClassID int
    	constraint FK_ClassID FOREIGN KEY REFERENCES Class (ClassID)
    )

    On the other hand, if you want to use a composite primary key (formed of more than one field), please refer to following SQL tutorial on how to create composite primary key on SQL Server

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 13, 2014 1:25 AM
  • User-750935311 posted

    I spoke to a SQL guy at work and his advice follows:  It is possible to have a table comprised of foreign keys only.  And it is possible to have all of them primary keys.  However, he added that whenever a field is defined as primary then the entire table will re-sort on every insert, for each primary key.  So, for example, if a record is added with one field value of 100, then another of 200, and then a third of 150, the entire table will re-index.  That could quickly cause performance issues depending on the frequency of inserts and the number of primary keys.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 14, 2014 12:41 AM

All replies

  • User-1360095595 posted

    Composite primary key. Make the primary key be the combination of all four columns. I assume the combination of the 4 columns cannot repeat in the table. 

    Sunday, January 12, 2014 5:21 PM
  • User364663285 posted

    You can use an Identity column as the PK to define each record.

    Sunday, January 12, 2014 11:30 PM
  • User-595703101 posted

    Hi Tualatin,

    Your sentence about SQL Server 2012, that is restricts you to use one foreign key only is false.

    You can define different foreign keys in a table. Please check it again you are possible doing something wrong if you are using SQL Server Management Studio

    If you are using SQL DDL for creating your table, you can use one similar as shown below

    create table Dojos(
    	DojoID int
    	constraint PK_DojoID PRIMARY KEY (DojoID)
    )
    create table Family(
    	FamilyID int
    	constraint PK_FamilyID PRIMARY KEY (FamilyID)
    )
    create table Members(
    	MemberID int
    	constraint PK_MemberID PRIMARY KEY (MemberID)
    )
    create table Class(
    	ClassID int
    	constraint PK_ClassID PRIMARY KEY (ClassID)
    )
    
    create table AikidoDojos (
    	DojoID int
    	constraint FK_DojoID FOREIGN KEY REFERENCES Dojos (DojoID),
    
    	FamilyID int
    	constraint FK_FamilyID FOREIGN KEY REFERENCES Family (FamilyID),
    
    	MemberID int
    	constraint FK_MemberID FOREIGN KEY REFERENCES Members (MemberID),
    
    	ClassID int
    	constraint FK_ClassID FOREIGN KEY REFERENCES Class (ClassID)
    )

    On the other hand, if you want to use a composite primary key (formed of more than one field), please refer to following SQL tutorial on how to create composite primary key on SQL Server

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 13, 2014 1:25 AM
  • User-750935311 posted

    I spoke to a SQL guy at work and his advice follows:  It is possible to have a table comprised of foreign keys only.  And it is possible to have all of them primary keys.  However, he added that whenever a field is defined as primary then the entire table will re-sort on every insert, for each primary key.  So, for example, if a record is added with one field value of 100, then another of 200, and then a third of 150, the entire table will re-index.  That could quickly cause performance issues depending on the frequency of inserts and the number of primary keys.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 14, 2014 12:41 AM
  • User-595703101 posted

    Re-index will not occur again until you explicitly run re-index command. We frequently reindex indexes since a fragmentation occurs as time passes because of update, delete and inserts on the table.

    If your declare a cluster index, than index data so the table data needs to be placed in different data pages and fragmentation will occur faster.

    Tuesday, January 14, 2014 1:24 AM