[imp] oracle_create script

[Corisen]

----- Original Message -----
From: Hanser, Kevin <kevin@ShopsForMe.com>
To: <imp@lists.horde.org>
Sent: Friday, October 13, 2000 1:43 AM
Subject: RE: [imp] oracle_create script


> 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.
>
>
>
> --
> IMP mailing list: http://horde.org/imp/
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: imp-unsubscribe@lists.horde.org
>