Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: SQLite : Foreign Key

    I'd like to create a foreign key. The Unit table should have a "unitID + BuildingID" as the primary foreign/composite key. So, I guess my problem is in the first line of the Unit Table.
    Code:
         CREATE TABLE Building (
    			buildingid      INTEGER PRIMARY KEY,                     
                            name           VARCHAR(50),                             
                            address        VARCHAR(50),                             
                            city           VARCHAR(50),                                 
                            postalcode     VARCHAR(50),                                  
    			province       VARCHAR(30),
    			description    VARCHAR(100),
    			imgpath        VARCHAR(150),
    			numberofunits  INTEGER,
    			floors		   INTEGER,
    			size		   DOUBLE);        
    
         CREATE TABLE Unit (
    			(unitID, BUILDINGID)Foreign Key Building
    			UnitID         INTEGER,                     
                            CurrOccID      VARCHAR(50),                             
                            size       VARCHAR(50),                             
                            Cost           DOUBLE,                                 
                            imgPath        VARCHAR(50),                                  
    			comment        VARCHAR(30),
    			);

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    "unitID + BuildingID" as the primary foreign/composite key.
    I don't understand this. (unitid, buildingid) as the primary key sounds a bit odd.
    It smells (without knowing more) that you want unitid as the PK for the unit table (but this is pure speculation based on the names you have chosen)

    Why do you think unitid needs to go into the foreign key? A foreign key consists of the same columns as the referenced primary key. In your case this is only buildingid.

    I don't really know SQLite but in standard SQL this would be written as:

    Code:
    CREATE TABLE Unit 
    (
      unitID      INTEGER NOT NULL,
      BUILDINGID  INTEGER NOT NULL, 
      CurrOccID   VARCHAR(50),                             
      size        VARCHAR(50),                             
      imgPath     VARCHAR(50),                                  
      comment     VARCHAR(30),
      PRIMARY KEY (unitid, buildingid),
      FOREIGN KEY (buildingid) REFERENCES building
    );

  3. #3
    Join Date
    May 2008
    Posts
    97
    You have made me reconsider my database design! and I thank you for this. You've also shown me how to do 2 awesome things.

    Thank you again!

  4. #4
    Join Date
    May 2008
    Posts
    97
    After some thinking I decided you were probably right about unitID. So, I modded my schema.. I'm hoping this is proper SQL syntax. Is this what you meant?

    CREATE TABLE Unit
    (
    unitID INTEGER NOT NULL PRIMARY KEY,
    BUILDINGID INTEGER NOT NULL,
    CurrOccID VARCHAR(50),
    size VARCHAR(50),
    imgPath VARCHAR(50),
    comment VARCHAR(30),
    FOREIGN KEY (buildingid) REFERENCES building
    );

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Looks better to me

    One note however: you should have a consistent way of using upper/lower case for column names.

    Either write all uppercase (BUILDINGID) or all lowercase (buildingid) or all camelcase (unitID).

    But do not mix different styles

Posting Permissions

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