none
Using composite key as foreign key

    Question

  • hi,

    I am an amateur at this and have been searching everywhere for my answer. Unfortunately, I do not understand most of them so I decided to just state the whole problem and hope you can assist. I am supposed to have 3 tables:owner,property and payment. The owner table has a pk TRN that is the fk in the other tables,its fine. But my issue is with the property and payment. The property table is to have a composite key of volume and the folio fields. I had the keys appear beside both fields. Both these fields also appear in the payment table thus they are to be fk's. Whenever creating the relationship by dragging both fields over to the payment table, its is fine. But when I look at it in database view, I am not seeing the related fields or values in the property table. The payment table only shows the relationship with the owners table, which is trn. Since the assignment asked for composite keys, I hope you can assist me with this. 

    Saturday, November 09, 2013 8:20 PM

All replies

  • There is no problem with this scenario. This is a sample code you can try:

    --create table dbo.property
    CREATE TABLE dbo.property
        (
          volume INT NOT NULL ,
          folio INT NOT NULL
        )
    GO
    
    --creat composit pk on dbo.property
    ALTER TABLE dbo.property ADD CONSTRAINT
    PK_property PRIMARY KEY CLUSTERED 
    	(
    	volume,
    	folio
    	) 
    GO
    
    --create table dbo.payment
    CREATE TABLE dbo.payment
        (
          volume INT NOT NULL ,
          folio INT NOT NULL
        )
    ON  [PRIMARY]
    GO
    
    --create composit fk on dbo.payment
    ALTER TABLE dbo.payment ADD CONSTRAINT
    FK_payment_property FOREIGN KEY
    	(
    	volume,
    	folio
    	) 
    REFERENCES dbo.property
    	(
    	volume,
    	folio
    	) 
    GO
    

    And this is the screenshot:


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Proposed as answer by KH MR Saturday, November 09, 2013 8:47 PM
    Saturday, November 09, 2013 8:44 PM
  • Hi 

    Another method would be having a surrogate key ... Have an IDENTITY col on the main table and referenced ID column in the Child table , create a UNIQUE constraint on volume and folio to enforce uniqueness.

    Sunday, November 10, 2013 9:37 AM
  • Thanks. I will try that. However, is there a way I can overcome the problem without using sql server? Just normal table relationships and integrity enforcing? 
    Sunday, November 10, 2013 1:52 PM
  • So just in normal microsoft access, how would I solve this? 
    Sunday, November 10, 2013 2:21 PM
  • It's the same in Access. Mark the primary key columns in the relationship window and drag them to the foreign key columns.
    Sunday, November 10, 2013 2:26 PM
  • 
    Sunday, November 10, 2013 2:43 PM
  • Sunday, November 10, 2013 2:44 PM
  • imho you have a design problem, but it dependes on your requirements. Normally there should not be a relationship between tblOwnern and tblPayment. Cause you need a relationship between tblProperty and tblPayment consisting of the three columns volume, folio and TRN.
    Sunday, November 10, 2013 5:15 PM
  • Composite (or compound?) keys are simple. Do you use (longitude, latitude) on a map? In full ANSI/ISO Standard SQL you can actually use a row constructor instead of separate columns. 

    They can in two patterns. The dimensions are independent like (longitude, latitude) or dependent like (category, sub-category). 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, November 10, 2013 10:09 PM