SQLite : Foreign Key

Eric the Red · #1 (**permalink**) 09-23-09, 16:33

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),
			);

shammat · #2 (**permalink**) 09-23-09, 17:06

Quote:

"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
);

Eric the Red · #3 (**permalink**) 09-24-09, 09:14

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!

Eric the Red · #4 (**permalink**) 09-24-09, 09:37

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
);

shammat · #5 (**permalink**) 09-24-09, 09:47

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

Internet Services

Web Development

Digital Marketing

Consumer Tech