Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

what type of constraint is this?

Status
Not open for further replies.

mike2228

Programmer
Dec 13, 2010
5
US
Let's say I am building a simple Content Management System (CMS), there would be a table "Article" that contains:
id (int, Primary key)
title (varchar)
content (some large varchar)
version (int)
guid (unique guid, or even number)

When an article is edited, a new record is inserted with a new id, the new record has incremented the version, and the guid is the same as previous versions of this article. So, if you wanted to get the "current" version of the article, we could query the table to get the article where title=ABC and version is the greatest number in the set of guids of the associated versions. So......

If I wanted another table "Top articles" (id, article_guid) that has a field "article_guid" to be associated with whichever is the most recent version of that article.... what type of constraint would it be to ensure that all inserts into Top Articles will always have an article_guid that exists in the Article table?

Thanks in advance!

-Mike
 
This is called "Referential Integrity". Depending on the system(s) involved, it could be Declarative RI or the RI might need to be reinforced by a Trigger, or worse yet application code. If trigger, then the trigger would check that the value exists in the lookup table. If declarative, then the database will enforce the lookup. You would also want to make guid of the Article table NOT NULL.

====================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
Thanks for the information.

Could you post some SQL code to create the RI constraint?
I've seen it done for assocaited a FK to a PK (1-to-1), but I have not seen the constraint code for the scenario that I've described. (I would like to avoid Triggers or Stored Procedures... my preference is to have the constraint on the table and not implemented by code)

Thank you in advance!
 
Something like WHERE EXISTS (SELECT Article.guid from Article where TopArticles.guid = Article.guid)

====================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top