Hi Tom,
I don't know if this will help you but the only way I've found to do what I
understand you are trying to do is to use a view based on the relevant data
in the table:
For example, in my case, I have GroupNumber and GroupSequence columns - both
integers. Say that the number can be between 1001 and 9999 and the sequence
between 11 and 99.
The view contains (at least) the two columns and a computed column: (
(GroupNumber * 100) + GroupSequence ).
You can then create your rule based on the original table and the
relationship to the view as you need - does exist, doesn't exist, must
exist, whatever...
Remember that the view is always up to date so there's a difference between
adding a row and after a row is added.
It's not as elegant as a constraint but it returns a result very quickly and
it might work for you if nothing else does.
Good luck & regards,
Alastair.
----- Original Message -----
From: "Tom Grimshaw" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, October 26, 2002 10:09 PM
Subject: FK to PK on same table MkII
> G'day,
>
> I said:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I am wanting to create a parent child relationship
> between trading entities in the same table so I can
> allow users to report on just one trading entity or
> all under multiple levels of holding companies.
>
> I have created the table TradingEntity and the PK
> col is EntityID. I have just programmatically
> added the col ParentEntityID to the table with a
> FK on ParentEntityID to reference EntityID.
>
> When I try to make another structural change in the
> object manager (delete SYS_ROWVER) I get the error
> message [The referenced table TradingEntity does not
> have a compatible Primary Key.]
>
> Which is the bug, allowing me to programmatically
> create the FK or not allowing me to edit the table
> after it is done?
>
> I know I can do it with a RULE, just interested to
> know the "proper" way to do it in R:BASE.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> But I was wrong.
>
> With this:
>
> CREATE TABLE `TradingEntity` +
> (`EntityID` INTEGER NOT NULL +
> ('Value for EntityID cannot be null.') , +
> `Org_ID` INTEGER NOT NULL +
> ('Value for Org_ID cannot be null.') , +
> `HeaderReqs` TEXT (70) , +
> `EntityName` TEXT (70) , +
> `AddressID` INTEGER , +
> `CNRowIDTel` INTEGER , +
> `CNRowIDFax` INTEGER , +
> `CNRowIDEmail` INTEGER , +
> `CNRowIDURL` INTEGER , +
> `EntityNote` TEXT (200) , +
> `CrntAvailable` TEXT (1) DEFAULT 'Y' NOT NULL , +
> `ParentEntityID` INTEGER , +
> `SYS_ROWVER`= +
> (IFNULL((SYS_ROWVER+1),0,(SYS_ROWVER+1))+
> ) INTEGER )
> ALTER TABLE `TradingEntity` ADD PRIMARY KEY +
> (`EntityID` ) +
> ('Values for rows in TradingEntity must be unique.',+
> 'Cannot delete - values exist in another table.',+
> 'Cannot update - values exist in another table.')
> AUTONUM `EntityID` IN `TradingEntity` USING +
> 4. 1. NONUM
> RULES 'Value must exist in TradingEntity ' +
> FOR `TradingEntity` SUCCEEDS +
> WHERE EXISTS (SELECT EntityID FROM TradingEntity WHERE T+
> radingEntity.EntityID = TradingEntity.ParentEntity+
> ID )
>
> In the browser I cannot get the existing values
> for EntityID accepted into ParentEntityID without
> a rule violation message on exit from the last
> row in the browser.
>
> Any clues?
>
> Warmest regards,
>
>
> Tom Grimshaw
> coy: Just For You Software
> tel: 612 9552 3311
> fax: 612 9566 2164
> mobile: 0414 675 903
>
> post: PO Box 470 Glebe NSW 2037 Australia
> street: 3/66 Wentworth Park Rd Glebe NSW 2037
>
> email: [EMAIL PROTECTED]
> web: www.just4usoftware.com.au
>
> "... the control of impulse -- is the first principle of civilization."--
> Will Durant,
> Pulitzer Prize winning philosopher, writer and historian
>
> the most needed product in the world can be found at
> www.thewaytohappiness.org
>
> This email and any files transmitted with it are confidential to the
> intended recipient and may be privileged. If you have received this email
> inadvertently or you are not the intended recipient, you may not
> disseminate, distribute, copy or in any way rely on it. Further, you
should
> notify the sender immediately and delete the email from your computer.
> Whilst we have taken precautions to alert us to the presence of computer
> viruses, we cannot guarantee that this email and any files transmitted
with
> it are free from such viruses.
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
