CODEWITHSUNDEEP
sqlsql-server
johan
johan

Reputation: 6666

Database: One To Many (or One To None) relationship

Im modelling a database in MSSQL 2008.

I have 4 tables. 

**User**
userID
userName

**NewsCategory**
newsCategoryID
newsCategoryName

**News**
newsID
newsText
newsCategoryID

**Subscription**
userID
categoryID

I understand that I should have foreign keys between the News and the Category tables. But what should I do with the supscriptions? Should I have a Foreign Key between User and Subscription tables though it's not mandatory to subscribe for something?

Upvotes: 3

Views: 3244

Answers (5)

chopikadze
chopikadze

Reputation: 4239

Yes you should. Foreign key is used for be sure, that Subscription is created for existing user. Foreign key does not mean, that user should be subscribed on something.

Upvotes: 7

Tim
Tim

Reputation: 5421

You've gotten some good answers. Let me try to add another.

A SUBSCRIPTION requires both a subscriber and a category. Therefore, each of these columns should not allow nulls. Preventing nulls is not the same thing as a foreign key constraint.

It should also be impossible to insert a row into SUBSCRIPTIONS if the user does not already exist in the USERS table; and it should be impossible to insert a row into SUBSCRIPTIONS if the category does not already exist in the CATEGORIES table. To enforce these rules your SUBSCRIPTIONS table requires two foreign key constraints:

ALTER TABLE SUBSCRIPTIONS ADD CONSTRAINT FK_SUBSCRIPTIONS_USERS FOREIGN KEY(userid) REFERENCES USERS(userid) ALTER TABLE SUBSCRIPTIONS ADD CONSTRAINT FK_SUBSCRIPTIONS_CATEGORIES FOREIGN KEY(categoryid) REFERENCES CATEGORIES(categoryid)

When you create a foreign key constraint on a table, you are in effect saying to the database engine: make sure that any value that gets inserted into this table already exists in that other table. BTW, a requirement for the constraint to be created is that a unique constraint must be in effect on the column(s) referenced in that table; typically, the referenced column(s) of that table will be the primary key of that table.

By creating a foreign key constraint, you are not saying to the database engine: make sure a row gets inserted into this table. It is quite possible (though it would be unusual) that this table has no rows in it whatsoever. The foreign key constraint simply makes sure that any value that does get inserted into this table has a counterpart in that table.

Upvotes: 0

gbn
gbn

Reputation: 432230

Subscription is a link (many-many) table and "not mandatory" means there will no row for that user or that user/category.

The foreign key is required to enforce data integrity when you do have subscriptions which will be one or more rows.

Note: In optional parent-child type relationships the FK column(s) will be NULLable to capture "non mandatory". In link tables this is captured by row non-existence

Upvotes: 2

CharithJ
CharithJ

Reputation: 47520

Yes, you should add Foreign keys between User and SubCription tables with Subscription table.

Foreign key contraints are for the validating of adding wrong information to the database. For example, in your Subscription table, there shouldn't be userIDs which are not in the User table and there should be CategoryIDs which are not in the NewsCategory table. These contraints will do the validation for you even if you don't do the validation at the user interface end.

Upvotes: 0

maple_shaft
maple_shaft

Reputation: 10463

Yes you should have this foreign key because it will prevent a Subscription from existing that does not map to a real user id.

It acts as a constraint on your data.

Upvotes: 2

Related Questions