Joseph Morgan - asks HOW TO work with Foreign Keys?

[Joseph Morgan]

Thank you guys for helping on this one. I will look at these options. :)

Joseph

> On Oct 13, 2013, at 11:52 AM, Sergey Pashkov <sergey_pashkov at valentina-db.com> wrote:
> 
> 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
> 
> _______________________________________________
> 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/76ea983a/attachment-0001.html>