[imp] oracle_create script

[Hanser, Kevin]

Ah, then it's probably a good idea to have them :)

here's the updated script:

*** horde_create.sql ***
# created 09-2000, by Kevin Hanser, by taking the create script for MySQL
# and modifying it to work w/Oracle datatypes... using the oracle_create
# as a guide
# tested with IMP 2.2.x, Oracle 8i (8.1.5, 8.1.6)

CREATE TABLE active_sessions (
    sid varchar2(32) NOT NULL,
    name varchar2(32) NOT NULL,
    val varchar2(4000),
    changed varchar2(14) NOT NULL,
    constraint PK_ACTIVE_SESSIONS primary key (NAME, SID)
);

create index CHANGED on ACTIVE_SESSIONS (CHANGED asc)


# create the address table
CREATE TABLE imp_addr (
    username varchar2(255),
    address varchar2(255),
    nickname varchar2(255),
    fullname varchar2(255)
);


# now create the pref table
CREATE TABLE imp_pref (
    username varchar2(255),
    sig varchar2(2000),
    fullname varchar2(70),
    replyto varchar2(70),
    lang varchar2(30)
);

# added 11-2000, after Hernan Morales pointed out that I forgot these
# (theyre in the original oracle_create.sql script)

ALTER TABLE IMP_ADDR ADD (
      CONSTRAINT PK_IMP_ADDR
      PRIMARY KEY (USERNAME,
                   ADDRESS)
);

ALTER TABLE IMP_PREF ADD (
      CONSTRAINT PK_IMP_PREF
      PRIMARY KEY (USERNAME)
);

ALTER TABLE IMP_ADDR ADD (
      CONSTRAINT FK_PREF_ADDR
      FOREIGN KEY (USERNAME)
      REFERENCES  IMP_PREF (
                   USERNAME)
);

*** end of horde_create.sql ***

Kevin


-----Original Message-----
From: C. R. Oldham [mailto:cro@nca.asu.edu]
Sent: Thursday, October 12, 2000 11:33 AM
To: imp@lists.horde.org
Subject: Re: [imp] oracle_create script


"Hanser, Kevin" wrote:

> And the mysql_create.sql script
> didn't have these constraints... does anybody know why?

The primary key constraints are supported by Mysql.  I don't recall the
original
script, but Mysql can require primary key fields to be unique (some would
say
that is redundant--that the definition of a primary key is that it is unique
but
I've worked with some DBs that support non-unique primary keys).  Mysql
doesn't
support referential integrity constraints.  It's mainly an artifact of
Mysql's
lack of stored procedures and triggers, which is how most databases
implement
referential integrity constraints

> Why would these be
> needed w/oracle but not w/other sql databases?

Without them, your tables can get out of sync--you get orphaned records in
the
child tables or parent records with no corresponding child records.  Or
multiple
records with the same primary key.