what type of constraint is this?

[mike2228]

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

 

[johnherman]

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

 

[mike2228]

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!

 

[johnherman]

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