Uniquely adding keywords to a "cross-reference table"

This is the place to post technical queries about SQL Yoga

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 888
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Uniquely adding keywords to a "cross-reference table"

Post by trevordevore » Fri Oct 11, 2013 2:02 pm

[This post is an archive from old SQL Yoga forums]

In my keywords "cross-reference table":

Code: Select all

   tblrelation_createObject "handler to keywords" 
   tblrelation_set "handler to keywords", "type", "many-to-many"
   tblrelation_set "handler to keywords", "left table", "handler" 
   tblrelation_set "handler to keywords", "left table key", "id"
   tblrelation_set "handler to keywords", "cross-reference table", "handler_keywords" 
   tblrelation_set "handler to keywords", "cross-reference table key for left table", "handler_id" 
   tblrelation_set "handler to keywords", "cross-reference table key for right table", "keyword_id"
   tblrelation_set "handler to keywords", "right table", "keywords" 
   tblrelation_set "handler to keywords", "right table key", "id"
   tblrelation_set "handler to keywords", "order by", "handler.name"
The left and right tables are primary keys and therefore unique, but i can't figure a fast way to add a keyword once without checking first to see what keywords are already associated with a handler which is slow - is there a fast way to ensure that the link table can only have unique combinations of "handler_id" and "keyword_id" so i can;t have more than one "array" keyword linked to a given handler for instance?
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 888
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Uniquely adding keywords to a "cross-reference table"

Post by trevordevore » Fri Oct 11, 2013 2:02 pm

Aha - came across the way to solve this. Seems like it is a common problem for db designers - this is a good reference - http://weblogs.sqlteam.com/jeffs/archiv ... _keys.aspx

What is needed is to create a composite primary key for the cross-reference table, that way each record is forced to be unique and as this is tied to the "handler_id" and "keyword_id" (and not some arbitrary GUID), we can ensure that each keyword is added only once to a handler. The change is to use the following CREAT sql:

Code: Select all

CREATE TABLE 'handler_keywords' ('handler_id' INTEGER NOT NULL, 'keyword_id' INTEGER NOT NULL, PRIMARY KEY ('handler_id', 'keyword_id'));
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 888
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Uniquely adding keywords to a "cross-reference table"

Post by trevordevore » Fri Oct 11, 2013 2:03 pm

That is the solution I was going to recommend. Just make sure you wrap the SQL Yoga call that tries to insert the record in a try/catch statement as an error will be thrown when the insert fails to a duplicate primary key.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

Post Reply

Return to “SQL Yoga”