<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div>Thank you guys for helping on this one. I will look at these options. :)</div><div><br></div><div>Joseph</div><div><br>On Oct 13, 2013, at 11:52 AM, Sergey Pashkov <<a href="mailto:sergey_pashkov@valentina-db.com">sergey_pashkov@valentina-db.com</a>> wrote:<br><br></div><blockquote type="cite"><div><div dir="ltr"><div>It is better, as Ruslan has said before, to use sequences with Foreign Keys.</div><div><br></div><div>More complete example with this approach will be:</div><div><br></div><div>CREATE TABLE "Invoices"(</div>
<div> "InvoiceDate" DATE,</div><div> "rowID" SERIAL32,</div><div> PRIMARY KEY( "rowID" ) );</div><div><br></div><div>CREATE TABLE "InvoiceItems"(</div><div> "InvoiceItemName" VARCHAR( 255 ),</div>
<div> "InvoiceKey" LONG,</div><div> "rowID" SERIAL32,</div><div> PRIMARY KEY( "rowID" ) );</div><div><br></div><div>ALTER TABLE "InvoiceItems" ADD CONSTRAINT "lnk_InvoiceItems_Invoices" </div>
<div><span class="" style="white-space:pre"> </span>FOREIGN KEY( "InvoiceKey" )</div><div><span class="" style="white-space:pre"> </span>REFERENCES "Invoices" ( "rowID" )</div><div><span class="" style="white-space:pre"> </span>ON DELETE RESTRICT </div>
<div><span class="" style="white-space:pre"> </span>ON UPDATE SET DEFAULT;</div><div><br></div><div><br></div><div>Field of SERIAL type creates own sequence automatically.</div><div><br></div><div>-----</div><div>RecIDs works naturally with ObjectPtr kind of links.</div>
<div><br></div><div>An equivalent example, which uses RecID-ObjectPtr approach looks like:</div><div><br></div><div>CREATE TABLE "Invoices"(</div><div> "InvoiceDate" DATE );</div><div><br></div>
<div>CREATE TABLE "InvoiceItems"(</div><div><span class="" style="white-space:pre"> </span>"InvoiceItemName" VARCHAR( 255 ),</div><div><span class="" style="white-space:pre"> </span>"InvoicePtr" OBJECTPTR INDEXED );</div>
<div><br></div><div>-- CREATE LINK "lnk_InvoiceItems_Invoices" ---------------------</div><div>ALTER TABLE "InvoiceItems" </div><div><span class="" style="white-space:pre"> </span>MODIFY "InvoicePtr" </div>
<div><span class="" style="white-space:pre"> </span>OBJECTPTR CONSTRAINT "lnk_InvoiceItems_Invoices" </div><div><span class="" style="white-space:pre"> </span>REFERENCES "Invoices" </div><div><span class="" style="white-space:pre"> </span>ON DELETE RESTRICT </div>
<div><span class="" style="white-space:pre"> </span>INDEXED;</div><div>-- -------------------------------------------------------------</div><div><br></div><div>Best regards,</div><div>Sergey Pashkov</div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Sun, Oct 13, 2013 at 7:18 PM, Sergey Pashkov <span dir="ltr"><<a href="mailto:sergey_pashkov@valentina-db.com" target="_blank">sergey_pashkov@valentina-db.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hello, Joseph,<div><br></div><div>You have to define either Primary Key or Unique constraint in the parent table before a Foreign Key creation.</div>
<div><br></div><div>So to get the last query working, you can declare structure of the second table:</div>
<div><div>CREATE TABLE "InvoiceItems"(</div><div> "InvoiceKey" ULONG <b>UNIQUE</b>,</div><div class="im"><div> "rowID" ULONG METHOD ( 'RecID' ) );</div></div></div><div>
<br></div><div>Or just make rowID method unique via the Property Inspector.</div>
<div><br></div><div>------------------------------------</div><div>But looking at the names of tables, I think you wanted to create a db schema,</div><div>where each row in InvoiceItems points to one parent row in Invoices1 table:</div>
<div><br></div><div><div class="im"><div><br></div><div>CREATE TABLE "Invoices1"(</div><div> "InvoiceDate" DATE,</div><div> "field1" ULONG,</div></div><div> "rowID" ULONG METHOD ( 'RecID' ) <b>UNIQUE</b> );</div>
<div class="im">
<div><br></div><div><br></div><div>CREATE TABLE "InvoiceItems"(</div><div> "InvoiceKey" ULONG,</div><div> "rowID" ULONG METHOD ( 'RecID' ) );</div><div><br></div></div>
<div>-- CREATE LINK "lnk_InvoiceItems_Invoices12" -------------------</div>
<div>ALTER TABLE "InvoiceItems" ADD CONSTRAINT "lnk_InvoiceItems_Invoices12" </div><div><span style="white-space:pre-wrap"> </span>FOREIGN KEY( "InvoiceKey" )</div><div><span style="white-space:pre-wrap"> </span>REFERENCES "Invoices1" ( "rowID" )</div>
<div class="im">
<div><span style="white-space:pre-wrap"> </span>ON DELETE RESTRICT </div><div><span style="white-space:pre-wrap"> </span>ON UPDATE SET DEFAULT;</div><div>-- -------------------------------------------------------------</div>
</div></div><div><br></div><div>Best regards,</div><div>Sergey Pashkov</div><div><br></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Sun, Oct 13, 2013 at 6:47 PM, Ruslan Zasukhin <span dir="ltr"><<a href="mailto:ruslan_zasukhin@valentina-db.com" target="_blank">ruslan_zasukhin@valentina-db.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>On 10/13/13 5:38 PM, "Joseph Morgan" <<a href="mailto:joseph@checkos.com" target="_blank">joseph@checkos.com</a>> wrote:<br>
<br>
Hi Joseph,<br>
<br>
</div><div>> I tried to create the link in Vstudio.<br>
><br>
> Two Tables as example:<br>
><br>
> CREATE TABLE "Invoices1"(<br>
> "InvoiceDate" DATE,<br>
> "field1" ULONG,<br>
> "rowID" ULONG METHOD ( 'RecID' ) );<br>
><br>
><br>
> CREATE TABLE "InvoiceItems"(<br>
> "InvoiceKey" ULONG,<br>
> "rowID" ULONG METHOD ( 'RecID' ) );<br>
<br>
</div>Hi Joseph,<br>
<br>
For FK-links, I will recommend use not CREATE LINK command,<br>
But something standard what have all other dbs.<br>
<br>
Example:<br>
<a href="http://www.w3schools.com/sql/sql_foreignkey.asp" target="_blank">http://www.w3schools.com/sql/sql_foreignkey.asp</a><br>
<br>
<br>
<br>
<br>
CREATE TABLE "InvoiceItems"(<br>
"InvoiceKey" ULONG FOREIGN KEY REFERENCES Persons(rowID),<br>
<div> "rowID" ULONG METHOD ( 'RecID' )<br>
);<br>
<br>
<br>
<br>
</div>===================<br>
Important notes:<br>
<br>
** If you want true ROWID, you can use new in 5.0 feature<br>
SEQUENCES, like in Postgre<br>
<br>
<br>
** if it is okay to use RecID itself, then you may want use<br>
RecID-ObjectPtr combination.<br>
<br>
It is more simple and more effective that FK-link.<br>
<div><div><br>
<br>
<br>
--<br>
Best regards,<br>
<br>
Ruslan Zasukhin<br>
VP Engineering and New Technology<br>
Paradigma Software, Inc<br>
<br>
Valentina - Joining Worlds of Information<br>
<a href="http://www.paradigmasoft.com" target="_blank">http://www.paradigmasoft.com</a><br>
<br>
[I feel the need: the need for speed]<br>
<br>
<br>
_______________________________________________<br>
Valentina mailing list<br>
<a href="mailto:Valentina@lists.macserve.net" target="_blank">Valentina@lists.macserve.net</a><br>
<a href="http://lists.macserve.net/mailman/listinfo/valentina" target="_blank">http://lists.macserve.net/mailman/listinfo/valentina</a><br>
<br>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></blockquote><blockquote type="cite"><div><span>_______________________________________________</span><br><span>Valentina mailing list</span><br><span><a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a></span><br><span><a href="http://lists.macserve.net/mailman/listinfo/valentina">http://lists.macserve.net/mailman/listinfo/valentina</a></span><br></div></blockquote></body></html>