SQL Anywhere database - Foreign keys question

Author

Foreign keys question

Edgard L. Riba

2005-06-13, 8:23 pm

Hi,
If I have two tables: Child and Parent, with each table having a SysID
column as its primary key, and the child table has a ParentID which
corresponds to the Parent Table's sysID.

For example:

CREATE TABLE Orders (
sysID INTEGER AUTOINCREMENT PRIMARY KEY,
date_ordered DATE,
name CHAR(80)
);
CREATE TABLE Order_item (
sysID INTEGER AUTOINCREMENT PRIMARY KEY,
parentID INTEGER NOT NULL,
item_num SMALLINT NOT NULL,
-- When an order is deleted, delete all of its
-- items.
FOREIGN KEY (parentID)
REFERENCES Orders (parentID)
ON DELETE CASCADE
)

If I want to access my child table by parentID/sysID, do I need to create an
index on parentID,sysID, or would it be redundant, since I have a foreign
key relationship between the two tables?

Thanks,
Edgard

Joshua Savill

2005-06-13, 8:23 pm

Edgard,

sysID is the primary key on the two tables and parentID will be the foreign
key to the table 'Orders'. Adding an index on parentID or sysID would create
a redundant index. Your test give the following warning message:

Performance warning: Redundant index "test" for table "Order_item" in
database "asademo"

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:42adee21@forums
-1-dub...
> Hi,
> If I have two tables: Child and Parent, with each table having a SysID
> column as its primary key, and the child table has a ParentID which
> corresponds to the Parent Table's sysID.
>
> For example:
>
> CREATE TABLE Orders (
> sysID INTEGER AUTOINCREMENT PRIMARY KEY,
> date_ordered DATE,
> name CHAR(80)
> );
> CREATE TABLE Order_item (
> sysID INTEGER AUTOINCREMENT PRIMARY KEY,
> parentID INTEGER NOT NULL,
> item_num SMALLINT NOT NULL,
> -- When an order is deleted, delete all of its
> -- items.
> FOREIGN KEY (parentID)
> REFERENCES Orders (parentID)
> ON DELETE CASCADE
> )
>
> If I want to access my child table by parentID/sysID, do I need to create
> an index on parentID,sysID, or would it be redundant, since I have a
> foreign key relationship between the two tables?
>
> Thanks,
> Edgard
>

Breck Carter [TeamSybase]

2005-06-14, 7:23 am

Some comments...

(1) It has to be "DEFAULT AUTOINCREMENT"

(2) The target of a REFERENCES clause must be a primary key or unique
constraint in the parent table, so REFERENCES Orders (parentID) gives
"Column 'parentID' not found". If you change it to REFERENCES Orders
(sysID) it will work.

CREATE TABLE Orders (
sysID INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,
date_ordered DATE,
name CHAR(80)
);
CREATE TABLE Order_item (
sysID INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,
parentID INTEGER NOT NULL,
item_num SMALLINT NOT NULL,
-- When an order is deleted, delete all of its
-- items.
FOREIGN KEY (parentID)
REFERENCES Orders (sysID)
ON DELETE CASCADE
);

(3) There will be two separate indexes created automatically, on
Order_item.sysID and Order_item.parentID. A third index on both
columns may or may not help performance; you should test queries with
and without the index to be sure.

(4) Here is an alternative design that is somewhat more traditional;
the primary key index on Order_item will include orderID and itemID:

CREATE TABLE Orders (
orderID INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,
date_ordered DATE,
name CHAR(80)
);
CREATE TABLE Order_item (
orderID INTEGER NOT NULL,
itemID INTEGER NOT NULL DEFAULT AUTOINCREMENT,
item_num SMALLINT NOT NULL,
PRIMARY KEY ( orderID, itemID ),
-- When an order is deleted, delete all of its
-- items.
FOREIGN KEY (orderID)
REFERENCES Orders (orderID)
ON DELETE CASCADE
);

Breck

On 13 Jun 2005 13:35:45 -0700, "Edgard L. Riba" <elriba at rimith dot
com> wrote:

>Hi,
>If I have two tables: Child and Parent, with each table having a SysID
>column as its primary key, and the child table has a ParentID which
>corresponds to the Parent Table's sysID.
>
>For example:
>
>CREATE TABLE Orders (
> sysID INTEGER AUTOINCREMENT PRIMARY KEY,
> date_ordered DATE,
> name CHAR(80)
> );
>CREATE TABLE Order_item (
> sysID INTEGER AUTOINCREMENT PRIMARY KEY,
> parentID INTEGER NOT NULL,
> item_num SMALLINT NOT NULL,
> -- When an order is deleted, delete all of its
> -- items.
> FOREIGN KEY (parentID)
> REFERENCES Orders (parentID)
> ON DELETE CASCADE
> )
>
>If I want to access my child table by parentID/sysID, do I need to create an
>index on parentID,sysID, or would it be redundant, since I have a foreign
>key relationship between the two tables?
>
>Thanks,
>Edgard
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Edgard L. Riba

2005-06-14, 11:23 am

Hi Joshua,

Thanks for clearing that up...

Best regards,
Edgard

Edgard L. Riba

2005-06-14, 11:23 am

Hi Breck,

Thanks for responding.

Sorry for the bugs in the listing :-(

> (4) Here is an alternative design that is somewhat more traditional;
> the primary key index on Order_item will include orderID and itemID:
>
> CREATE TABLE Orders (
> orderID INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,
> date_ordered DATE,
> name CHAR(80)
> );
> CREATE TABLE Order_item (
> orderID INTEGER NOT NULL,
> itemID INTEGER NOT NULL DEFAULT AUTOINCREMENT,
> item_num SMALLINT NOT NULL,
> PRIMARY KEY ( orderID, itemID ),
> -- When an order is deleted, delete all of its
> -- items.
> FOREIGN KEY (orderID)
> REFERENCES Orders (orderID)
> ON DELETE CASCADE
> );

My original design was something like this, but I understand (thanks to a
friend) that for optimum performance, primary keys should be single
component, so I'm trying to do all new designs this way, even if I have to
add an additional index to access (parentID,childID).

Best regards,
Edgard L. Riba

Sponsored Links