SQL Commands for creating Binary Links from Legacy Linkvalues?

[Ivan Smahin]

Hello Barry,

Saturday, July 14, 2007, 11:03:28 AM, you wrote:

> Why not just to build RDB link?
> A: Happy to if that's the direction we want to take in our procedure to
> achieve Binary Link M:1.  This this I believe would be 
> Step 1 - Create Foreign Key Link.

> Later you can convert it to the BinaryLink for example.
> A: If I'm following correctly then this would be 
> Step 2 - Create Binary Link.

Right. Firstly you need to prepare target link (empty link).
Then you do "COPY LINKS" operation.

> But if you have "natural" keys why do you need BinaryLink at all?
> A: Table 1 has 10,000 ShopNo    and    Table 2 has 100,000,000 Receipts
>     I can index both ShopNo fields and Build a Left outer Join Select 
> Statement but with that many records I believe it would take a considerable
> about of time for even valentina to build a logical index on the fly.
> If I am mistaken here then I would be very very interested in know that.
> Because if I AM mistaken then I can stop here.

Dealing  with  FK  constraint  (RDB  link)  you  can not left FK field
not-indexed. So there is no need to build any index during join.
But  if  you  have  no link at all between tables (but only "logical" one) then
join  will  build something like "temporary  RDB-link"  each time.
There is one of the reasons why you need to build explicit link.

>> There is NO current Valentina key created.  Other than the RecIDs.

> There is no problem to create ones.
> A:  Acknowledged.  I'm just after the step-by-step procedure to get
> Valentina to create the 1,000,000,000 links for me thru SQL.

Look,  if  you have some data which seems to be keys so you can try to
build RDB link.

Example:
ALTER TABLE t1 Modify id ulong Indexed;
ALTER TABLE t2 Modify ptr ulong Indexed;

ALTER TABLE t1  ADD CONSTRAINT PK_t1  PRIMARY KEY ( id );
ALTER  TABLE  t2  ADD CONSTRAINT FK_t2_t1 FOREIGN KEY (ptr) REFERENCES
t1(id);


That is all!
You get RDB-link with name "FK_t2_t1".


>> Where the "COPY LINKS FROM src_link_name TO dest_link_name" 
>> I believe valentina expect src_link_name to be some sort of pre built
>> Valentina key.

> Valentina  expects  the  source  link  there.  Creating FK constraint,
> RDB-link with the same name is creating also.
> A: I missed that.  Keeping things in context, I think the src_Link_name
> would the Foreign Key built in Step 1.  As I understand FK constraints is to
> do with the Deletion of parent records. ?

Internally  FK-constraints  and RDB-link are different things. But for
user there are no differences.

So RDB-link and FK-constraint can ne considered as synonyms.


> A:  Can I ask, is the RDB-Link exactly the same at the FK creation?

Yes.

> If not then I'm not sure why we keep mentionion RDB instead of FK.  And more
> research will be required on my part.

FK-constraint is mentioned in SQL standards. There are no link definition at all.
So relational world operates with constraint which refuses or allows any
data modifications. So that records are virtually "linked".

Valentina introduced LINK concept. There are 3 link kinds:
 - ObjectPtr
 - Binary link
 - RDB link

When  you  create RDB-link, FK-constraint will be built internally. And
vice versa. That things live always in couple.

Shortly  -  you don't think about constraint. There is internal thing.
You should operate with links.
Don't be confused with something like "ADD CONSTRAINT FK_t2_t1 FOREIGN KEY"
You  may think of it in such a way: "RDB-link (because of FOREIGHN KEY) will
be created with name "FK_t2_t1" ".



-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com