Defining keys

Defining keys

Post by Ross Hayd » Sun, 30 Oct 1994 07:12:20

Can anyone give me a brief syntax summary of the way to define primary
or foreign keys using informix 4gl (version 4 I think).


Ross Hayden (a newbie Informix user, as if it wasn't obvious :)


Defining keys

Post by Dennis Pimp » Sat, 05 Nov 1994 03:13:13

>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:

    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);


    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.


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.


1. Defining Keys for Dimension Tables


We are in the process of designing dimensional model. We need to use a
sub-dimension table within one of  the dimensions. Our question is :

1. Can we have 2 primary keys(1.surrogate 2. the natural key) for the
dimension table.

2. How to link the sub-dimension with the natural-key.

E.g Member Master  has Membno as surrogate key  and
      Source_Membno as the natural key. (This contains
      Individual member details like name, address etc.)
      The sub-dimension has source_membno which needs
       to be linked to the source_membno of the member
       master. (This contains members eligibility details over
       time span, which keeps changing).


2. Problem installing Oracle XML-utility xsu111

3. Defining keys in SYBASE 11

4. PL/PgSQL buglet / doc error

5. Browsing and re-defining keys for the browse window!

6. Oracle 7 to UDB2 6.1 conversion too

7. Defining relations with Foreign keys in dimensional models

8. How execute several operations in a unique script

9. No primary keys defined

10. How use T-SQL Define Foreight Key??

11. How to Define Multi-Column Primary Key Constraint with T-SQL

12. Help: Define Foreign Key from a View

13. primary key (or unique defined) -> case sensitive