FW: snapshot and referential integrity

From: Richard Ji <richard_at_letsplay.com>
Date: Wed, 31 May 2000 09:54:18 -0400
Message-Id: <10514.107288@fatcity.com>

Anita,

        Thanks for the feedback. I am using 8iR2. I don't know if the restrictions on snapshot changed much in 8i since 8. At this point I am more leaning towards master/master(asynchronous) replication for the following reasons:
1. Setting up master/snapshot sites isn't any easier than master/master sites.
2. By using Asynchronous replication, tables in STAGE and LIVE are both real tables that don't have the restrictions snapshot has. 3. By using Asynchronous replication I can easily break up the relationship between STAGE and LIVE if I want to and they will be self-contained and can function on its own.

	Would love to hear your thoughts about this.
	Thanks.

Richard Ji

-----Original Message-----
From: A. Bardeen [mailto:abardeen1_at_yahoo.com] Sent: Wednesday, May 31, 2000 8:01 AM
To: ORACLE-L_at_fatcity.com; richard_at_letsplay.com Subject: Re: snapshot and referential integrity

Richard,

It depends on the version of Oracle used for the snapshot site. O7 snapshots do not support PK or unique constraints because Oracle doesn't guarantee that the records will be refreshed in the same order as the DML operations were performed on the master table, only that after refresh the rows will be the same. You can create indexes on the O7 snapshots, just not unique indexes. With the introduction of deferrable constraints in O8, PK and unique constraints are supported on snapshots as long as they are created DEFERRABLE.

BTW, this is relevant only to updateable snapshots. If you have read-only snapshots there's no point in having a PK constraint on the snapshot as DML operations aren't allowed on it anyway. You guarantee RI among snapshots by refreshing them together via refresh groups.

HTH,

• Anita
  • Richard Ji <richard_at_letsplay.com> wrote:
    > Hi all,
    > I am planning to setup the following: a staging
    > database STAGE and a live
    > database LIVE. Some tables in the LIVE database are
    > just regular tables
    > (that will get its data directly from web site)
    > others are snapshots from
    > the STAGE database. The reason for that is for
    > those tables we can enter
    > data into it's STAGE's master table and after QA it
    > then can be released
    > into LIVE via snapshot refresh.
    > So my question is: For those snapshots in the LIVE
    > database, can I have
    > referential integrity to it. For example, if DEPT
    > is a snapshot in LIVE,
    > can EMP has a foreign key to DEPT in the LIVE?
    > Thanks.
    >
    > Richard Ji
    >
    >
    > --
    > Author: Richard Ji
    > INET: richard_at_letsplay.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >
    

    ---

    
    > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > (or the name of mailing list you want to be removed
    > from). You may
    > also send the HELP command for other information
    > (like subscribing).
    

    ---

Do You Yahoo!? Received on Wed May 31 2000 - 08:54:18 CDT