Table relationships - please explain.

[baden]

In the past I have created my tables with PK and FK values, but not defined to the database, that is, I have written my own SQL to display the relationships between tables.

In the example below, I have the following tables:
Users, InterestCategory, InterestSubCategory, and UserInterestCategory.

The last table creates the relationships for users and their associated interest subcategories (which are related to the interest categories.

What I would normally do is use something like nested LEFT JOIN statements to grab all the data for say userID = 1.

== THE QUESTION ==
I've known of creating the FK relationships where the database is aware of these, but I'm just not sure how to use them, and what's the advantage (notice the Alter Table statements below)??? How does defining these relationships make my job easier?! (examples please)

Now that I'm playing around with Case Studio, I've defined the following (and see the PK/FK relationships and indices created in the db:

Drop table [T_Users] 
go
Drop table [T_UserInterestCategory] 
go
Drop table [T_InterestSubCategory] 
go
Drop table [T_InterestCategory] 
go


Create table [T_InterestCategory]
(
	[icID] Bigint Identity NOT NULL, UNIQUE ([icID]),
	[icDescription] Varchar(90) NULL,
Primary Key ([icID])
) 
go

Create table [T_InterestSubCategory]
(
	[iscID] Bigint Identity NOT NULL, UNIQUE ([iscID]),
	[icID] Bigint NOT NULL,
	[iscDescription] Varchar(90) NULL,
Primary Key ([iscID],[icID])
) 
go

Create table [T_UserInterestCategory]
(
	[uicID] Bigint Identity NOT NULL, UNIQUE ([uicID]),
	[userID] Bigint NOT NULL,
	[iscID] Bigint NOT NULL,
	[icID] Bigint NOT NULL,
Primary Key ([uicID],[userID],[iscID],[icID])
) 
go

Create table [T_Users]
(
	[userID] Bigint Identity NOT NULL, UNIQUE ([userID]),
	[UserName] Varchar(50) NULL, UNIQUE ([UserName]),
	[UserPassword] Varchar(50) NULL,
Primary Key ([userID])
) 
go



Alter table [T_InterestSubCategory] add  foreign key([icID]) references [T_InterestCategory] ([icID])  on update no action on delete no action 
go
Alter table [T_UserInterestCategory] add  foreign key([iscID],[icID]) references [T_InterestSubCategory] ([iscID],[icID])  on update no action on delete no action 
go
Alter table [T_UserInterestCategory] add  foreign key([userID]) references [T_Users] ([userID])  on update no action on delete no action 
go


Set quoted_identifier on
go

 

[vongrunt]

> I've known of creating the FK relationships where the database is aware of these, but I'm just not sure how to use them, and what's the advantage (notice the Alter Table statements below)??? How does defining these relationships make my job easier?! (examples please)

Suppose there are tables Persons and Countries. Persons has column CountryID logically linked to Countries table on it's unique/primary key.

Without foreign key on Persons.CountryID, you can enter any value there and database won't complain. Non-existant countryID, garbage data, whatever. Or delete record from Countries and leave buncha records from Persons in orphan state. In other words, data integrity may become screwed up.

With foreign key database guarantees such things will never happen. Any attempt to do wrong things will result with raised runtime error (FK violation).

Sure, all these checks can be done client-side or in middle-tier. But database does it better, centralized and totally declarative - you create FKeys and forget they ever exist.

Relationships can also do cascaded UPDATEs/DELETEs. This is sometimes useful, sometimes best to avoid for various reasons.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)