Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Creating an associative table between an existing associative table and another table

    This is the issue I'm having. I am trying to create an associative relationship between an existing associative table (who's primary key is the composite of two foreign keys based on primary keys in two other tables) and a regular table with a non-composite primary key. So far, I've had no success. Does anyone know how to do this?

    Here are the tables I want to create the associative relationship between:

    1)

    ABSTRACT_DOCUMENT
    AbstractId (PK, FK, int, not null)
    DocumentId (PK, FK, int, not null)
    Comments (varchar(100), null)
    Description (varchar(100), null)
    AttorneyNotes (varchar(100), null)

    2)

    TRACT
    TractId (PK, int, not null)
    TractName (varchar(4), not null)


    It looks like the final result would have to be a new table with a composite primary key that references the AbstractId and DocumentId primary keys in the ABSTRACT_DOCUMENT table and the TractId primary key in the TRACT table. Any assistance or suggestions would be greatly appreciated.

    Paul
    Paul Palubinski

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ppalubinski
    It looks like the final result would have to be a new table with a composite primary key that references the AbstractId and DocumentId primary keys in the ABSTRACT_DOCUMENT table and the TractId primary key in the TRACT table.
    Looks to me you've described exactly what you want to do. So what problem are you having?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    ABSTRACT_DOCUMENT_TRACT
    AbstractId (PK, FK, int, not null)
    DocumentId (PK, FK, int, not null)
    TractId (PK, FK, int, not null)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Thanks for the response, guys. I understand the concept, but was unable to implement it in DDL. I didn't know how to reference the composite key in the ABSTRACT.ABSTRACT_DOCUMENT table. Does anyone know how to write out the DDL for this to create the new table?
    Paul Palubinski

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by ppalubinski
    I understand the concept, but was unable to implement it in DDL. I didn't know how to reference the composite key in the ABSTRACT.ABSTRACT_DOCUMENT table. Does anyone know how to write out the DDL for this to create the new table?
    What have you come up to so far? Also give what you have for ABSTRACT_DOCUMENT and TRACT DDLs. It will be very similar.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's just like referencing a single column in a forein key constraint, you just reference two, comma separated.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    you just reference two, comma separated.
    example plz
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    example plz
    exmple tks
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.bars')) BEGIN
        DROP TABLE dbo.bars
    END
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.foos')) BEGIN
        DROP TABLE dbo.foos
    END
    
    CREATE TABLE dbo.foos
        (
              foo        INT        NOT NULL
            , bar        INT        NOT NULL
            , CONSTRAINT pk_foos PRIMARY KEY CLUSTERED (foo, bar) WITH (FILLFACTOR = 100)
        )
    GO    
    
    CREATE TABLE dbo.bars
        (
              foo        INT        NOT NULL
            , bar        INT        NOT NULL
            , plugh        INT        NOT NULL
            , CONSTRAINT pk_bars PRIMARY KEY CLUSTERED (foo, bar, plugh) WITH (FILLFACTOR = 100)
            , CONSTRAINT fk_bars_foos FOREIGN KEY (foo, bar) REFERENCES dbo.foos (foo, bar) ON DELETE CASCADE
        )
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brilliant, ta very much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Are you guys ready to be blown away by some awesome ddl (sarcasm)?

    This is what I've been trying:

    CREATE TABLE ABSTRACT.ABSTRACT_DOCUMENT_TRACT
    (
    AbstractId INT REFERENCES ABSTRACT.ABSTRACT_DOCUMENT(AbstractId), DocumentId INT REFERENCES ABSTRACT.ABSTRACT_DOCUMENT(DocumentId), Tract INT REFERENCES ABSTRACT.TRACT(TractId),

    CONSTRAINT pk_Abstract_Document_Tract PRIMARY KEY (AbstractId, DocumentId, TractId)
    )

    Here is the error:

    Msg 1776, Level 16, State 0, Line 1
    There are no primary or candidate keys in the referenced table 'ABSTRACT.ABSTRACT_DOCUMENT' that match the referencing column list in the foreign key 'FK__ABSTRACT___Abstr__3572E547'.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    I've also tried:

    CREATE TABLE ABSTRACT.ABSTRACT_DOCUMENT_TRACT
    (
    AbstractId INT REFERENCES ABSTRACT.ABSTRACT_DOCUMENT(AbstractId, DocumentId),
    TractId INT REFERENCES ABSTRACT.TRACT(TractId),
    CONSTRAINT pk_Abstract_Document_Tract PRIMARY KEY (AbstractId, DocumentId, TractId)
    )

    With the following error:

    Msg 8140, Level 16, State 0, Line 1
    More than one key specified in column level FOREIGN KEY constraint, table 'ABSTRACT.ABSTRACT_DOCUMENT_TRACT'.
    Msg 1911, Level 16, State 1, Line 1
    Column name 'DocumentId' does not exist in the target table or view.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.
    Last edited by ppalubinski; 03-12-09 at 14:45.
    Paul Palubinski

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look at my code - you must define composite foreign keys separately from the individual columns. It is exactly like the PK declaration in fact. I would argue that, for consistency, this is how you should specify ALL FK, PK and constraints (where allowed anyway) for consistency.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Okay, so I followed your code and the command executed successfully. This is the statement I used:

    create table aBSTRACT.ABSTRACT_DOCUMENT_TRACT
    (
    AbstractId INT NOT NULL,
    DocumentId INT NOT NULL,
    TractId INT NOT NULL,

    CONSTRAINT pk_Abstract_Document_Tract PRIMARY KEY CLUSTERED (AbstractId, DocumentId, TractId),
    CONSTRAINT fk_Abstract_Document FOREIGN KEY (AbstractId, DocumentId) REFERENCES ABSTRACT.ABSTRACT_DOCUMENT(AbstractId, DocumentId),
    CONSTRAINT fk_Tract FOREIGN KEY (TractId) REFERENCES ABSTRACT.TRACT(TractId)
    )

    Thanks a lot for sharing that, and sorry I didn't use it when you first posted it, but the first time through, I had trouble following it. Just a quick question, what does the CLUSTERED keyword do?
    Paul Palubinski

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Although PKs are declared as constraints, pretty well all RDBMSs enforce these by using indexes. It is the most efficient mechanism. An index is either clustered or nonclustered. You can have a single clustered index, and N nonclustered indexes. Typically, you will want your clustered index to be the PK with rare exceptions. Google or look in BoL for more details.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Okay, thanks. That is definitely something I need to look into as I know very little about it...
    Paul Palubinski

Posting Permissions

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