Re: Defining keys
rossh@ncc.uky.edu (Ross Hayden) writes:
>Can anyone give me a brief syntax summary of the way to define primary
>or foreign keys using informix 4gl (version 4 I think).
Keys, of course, are a database engine feature, not a programming
language feature, so it doesn't matter that you're using 4gl. If you
are using version 4.x or earlier of either database engine (Standard
Engine or OnLIne), there is no specific syntax for defining a primary
or foriegn key. The *smart* designers (like me!) make sure there is a
UNIQUE INDEX on any primary key, and a non-unique INDEX on any foriegn
key. So you have:
CREATE TABLE parent
(
a CHAR(##) NOT NULL,
b CHAR(##) NOT NULL,
c ....
);
Columns a and b are the PK for the table, so:
CREATE UNIQUE INDEX parent_1 ON parent(a,b);
then:
CREATE TABLE child
(
a CHAR(##) NOT NULL, {* link to parent.a *}
b CHAR(##) NOT NULL, {* link to parent.b *}
ct SMALLINT NOT NULL, {* completes child PK *}
d ....
);
Columns a and b are the FK to table parent, and a,b,ct are the PK for
child. So:
CREATE UNIQUE INDEX child_1 ON child(a,b,ct);
In this case there is no need to have an INDEX ON child(a,b), because
child_1 can be used for any joins to parent. In the case where a FK is
not part of the tables PK a non-unique index is appropriate:
CREATE INDEX child_2 ON child(a,b);
Other rules of thumb:
- All columns in a PK or FK should be NOT NULL if at all possible.
- Name columns in a join the same in both tables (like I did above).
There are rare exceptions to both these rules.
Finally, with 5.x and above database engines, there is additional
syntax for designating Primary and Foriegn keys, which I don't know off
the top of my head. See the Reference manual under "CREATE TABLE".
Essentially, though, they do the same thing as we did above: create
unique or non-unique indicies for the appropriate columns.
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.