Joseph Morgan - asks HOW TO work with Foreign Keys?

[Sergey Pashkov]

Hello, Joseph,

You have to define either Primary Key or Unique constraint in the parent
table before a Foreign Key creation.

So to get the last query working, you can declare structure of the second
table:
CREATE TABLE "InvoiceItems"(
        "InvoiceKey" ULONG *UNIQUE*,
        "rowID" ULONG METHOD ( 'RecID' ) );

Or just make rowID method unique via the Property Inspector.

------------------------------------
But looking at the names of tables, I think you wanted to create a db
schema,
where each row in InvoiceItems points to one parent row in Invoices1 table:


CREATE TABLE "Invoices1"(
        "InvoiceDate" DATE,
        "field1" ULONG,
        "rowID" ULONG METHOD ( 'RecID' ) *UNIQUE* );


CREATE TABLE "InvoiceItems"(
        "InvoiceKey" ULONG,
        "rowID" ULONG METHOD ( 'RecID' ) );

-- CREATE LINK "lnk_InvoiceItems_Invoices12" -------------------
ALTER TABLE "InvoiceItems" ADD CONSTRAINT "lnk_InvoiceItems_Invoices12"
FOREIGN KEY( "InvoiceKey" )
REFERENCES "Invoices1" ( "rowID" )
ON DELETE RESTRICT
ON UPDATE SET DEFAULT;
-- -------------------------------------------------------------

Best regards,
Sergey Pashkov



On Sun, Oct 13, 2013 at 6:47 PM, Ruslan Zasukhin <
ruslan_zasukhin at valentina-db.com> wrote:

> On 10/13/13 5:38 PM, "Joseph Morgan" <joseph at checkos.com> wrote:
>
> Hi Joseph,
>
> > I tried to create the link in Vstudio.
> >
> > Two Tables as example:
> >
> > CREATE TABLE "Invoices1"(
> > "InvoiceDate" DATE,
> > "field1" ULONG,
> > "rowID" ULONG METHOD ( 'RecID' ) );
> >
> >
> > CREATE TABLE "InvoiceItems"(
> > "InvoiceKey" ULONG,
> > "rowID" ULONG METHOD ( 'RecID' ) );
>
> Hi Joseph,
>
> For FK-links, I will recommend use not CREATE LINK command,
> But something standard what have all other dbs.
>
> Example:
>        http://www.w3schools.com/sql/sql_foreignkey.asp
>
>
>
>
> CREATE TABLE "InvoiceItems"(
>  "InvoiceKey" ULONG FOREIGN KEY REFERENCES Persons(rowID),
>   "rowID" ULONG METHOD ( 'RecID' )
> );
>
>
>
> ===================
> Important notes:
>
> **  If you want true ROWID, you can use new in 5.0 feature
>   SEQUENCES, like in Postgre
>
>
> ** if it is okay to use RecID itself, then you may want use
>        RecID-ObjectPtr   combination.
>
> It is more simple and more effective that FK-link.
>
>
>
> --
> Best regards,
>
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
>
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
>
> [I feel the need: the need for speed]
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20131013/e5c1bea5/attachment.html>