Joseph Morgan - asks HOW TO work with Foreign Keys?

[Sergey Pashkov]

It is better, as Ruslan has said before, to use sequences with Foreign Keys.

More complete example with this approach will be:

CREATE TABLE "Invoices"(
        "InvoiceDate" DATE,
        "rowID" SERIAL32,
        PRIMARY KEY( "rowID" ) );

CREATE TABLE "InvoiceItems"(
        "InvoiceItemName" VARCHAR( 255 ),
        "InvoiceKey" LONG,
        "rowID" SERIAL32,
        PRIMARY KEY( "rowID" ) );

ALTER TABLE "InvoiceItems" ADD CONSTRAINT "lnk_InvoiceItems_Invoices"
FOREIGN KEY( "InvoiceKey" )
REFERENCES "Invoices" ( "rowID" )
ON DELETE RESTRICT
ON UPDATE SET DEFAULT;


Field of SERIAL type creates own sequence automatically.

-----
RecIDs works naturally with ObjectPtr kind of links.

An equivalent example, which uses RecID-ObjectPtr approach looks like:

CREATE TABLE "Invoices"(
        "InvoiceDate" DATE );

CREATE TABLE "InvoiceItems"(
"InvoiceItemName" VARCHAR( 255 ),
"InvoicePtr" OBJECTPTR INDEXED );

-- CREATE LINK "lnk_InvoiceItems_Invoices" ---------------------
ALTER TABLE "InvoiceItems"
MODIFY "InvoicePtr"
OBJECTPTR CONSTRAINT "lnk_InvoiceItems_Invoices"
REFERENCES "Invoices"
ON DELETE RESTRICT
INDEXED;
-- -------------------------------------------------------------

Best regards,
Sergey Pashkov


On Sun, Oct 13, 2013 at 7:18 PM, Sergey Pashkov <
sergey_pashkov at valentina-db.com> wrote:

> 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/c309af26/attachment-0001.html>