Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to define composite key?

    Hello, everyone:

    I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
    1. T-SQL
    2. ERD

    Thanks a lot.

    ZYT

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The question is more complex than it seems, so the answer will be a bit "long winded".

    1) You define PK and FK using a contraint within Transact SQL. The constraint types are PRIMARY KEY and FOREIGN KEY. For example:
    Code:
    CREATE TABLE composite (
       compositeId INT NOT NULL
       CONSTRAINT XPKcomposite
          PRIMARY KEY (compositeId)
    ,  name VARCHAR(50) NOT NULL
       )
    
    CREATE TABLE component (
       componentId INT NOT NULL
       CONSTRAINT XPKcomponent
          PRIMARY KEY (componentId)
    ,  name VARCHAR(50) NOT NULL
       )
    
    CREATE TABLE membership (
       compositeId INT NOT NULL
       CONSTRAINT XFK01membership
          FOREIGN KEY (compositeId)
             REFERENCES composite (compositeId)
    ,  componentId INT NOT NULL
       CONSTRAINT XFK02membership
          FOREIGN KEY (componentId)
             REFERENCES component (componentId)
       CONSTRAINT XPKmembership
          PRIMARY KEY (compositeId, componentId)
       )
    This allows you to have many composites (packages), made up of many components (parts), and allows each component to appear in as many packages as needed (because the membership relationship is separate from both the component and the composite).

    The ERD diagram questions are a bit more complex. Using IDE1FX, any attribute "above the line" is part of the primary key. You can optionally tag the foreign key attributes with an (FK) designator. Using "crows foot" notation, any attribute "above the line" is also part of the primary key, but there is no standard way to denote foreign keys. Using the various GUI tools for UML, the rules vary.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable00(Col4 int NOT NULL PRIMARY KEY)
    
    
    CREATE TABLE myTable99 (
    	  Col1 int IDENTITY(1,1)
    	, Col2 char(1)
    	, Col3 datetime DEFAULT GetDate()
    	, Col4 int
    	, PRIMARY KEY (Col1, Col2)
    	, FOREIGN KEY (Col4) REFERENCES myTable00(Col4)
    )
    GO
    
    DROP TABLE myTable99
    DROP TABLE myTable00
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2003
    Posts
    223
    Hello, Pat and Brett:

    Thanks a lot for the posts. Let's share this code I got it from email.

    create table addresses (
    houseNum INT,
    telNo INT,
    constraint pk_address primary key (houseNum, telNo)
    )

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not clear on why you are sharing that. What would you like us to do with it?

    -PatP

Posting Permissions

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