RE: What this guy mean to say..
1. I believe that the index must exactly match the index which you are
creating the referential constraint on for it to create not create the index
on it. I have done no testing in this respect, so am just hypothesizing.
2. Even if Informix was good enough to figure out that you already had
an index, the columns you are creating the referential constraints on would
have to be the leading columns of the already existing index.
the PRIMARY KEY for sub_accounts would have to be on
"ref_num, ref_type, sub_acc" and the foreign key would be created
on "ref_num, ref_type".
The reason for this index is so that a record is deleted from the accounts
table the database can check and make sure that no records in the
sub_accounts table exist without doing a sequential scan through the
sub_accounts table.
On the topic of deciding not to have referential integrity I have some
comments.
I have seen people not create referential integrity with mixed results. If
the
programs which enter the data are tightly controlled, heavily tested, and
are required to process lots of data quickly it is an option. I feel there is
also a definite amount of danger. If any bugs make it through testing there
is nothing to cause the inserts to abort. Other processes, which trust the
data model to be accurate can then propagate this bogus information
throughout the system. This can cause more time to be spent fixing data
and explaining to users why the data is not valid.
I hope this helps,
Will
>
>CREATE TABLE accounts (
>acc_num INTEGER,
>acc_type INTEGER,
>acc_descr CHAR(20),
>PRIMARY KEY (acc_num, acc_type));>
>CREATE TABLE sub_accounts (
>sub_acc INTEGER,
>ref_num INTEGER,
>ref_type INTEGER,
>sub_descr CHAR(20),
>PRIMARY KEY (sub_acc, ref_num, ref_type));^^^
this would have to be PRIMARY KEY(ref_num, ref_type, sub_acc)
>
>>alter table sub_accounts add constraint FOREIGN KEY(ref_num, ref_type)
>REFERENCES accounts;
>===== Original Message From Sanjeev sagar <sanjeev.sagar@sabre.com> =====
>This is a multi-part message in MIME format.
>--------------BB06916111D1186D6207CFC3
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>As per the Informix Guide, When REFERENTIAL CONSTRAINTS is placed on a
column,
>the database server creates a non-unique index for the columns specified in
the
>referential constraint
>
>However, IF A CONSTRAINT IS ALREADY WAS CREATED ON THE SAME COLUMN OR SET OF
>COLUMNS, ANOTHE INDEX IS NOT BUILT FOR THE CONSTRAINTS. INSTEAD, THE EXISTING
>INDEX IS SHARED BY THE CONSTRAINTS.
>
>This is not happening. I have done a small follwoing test
>
>CREATE TABLE accounts (
>acc_num INTEGER,
>acc_type INTEGER,
>acc_descr CHAR(20),
>PRIMARY KEY (acc_num, acc_type));>
>CREATE TABLE sub_accounts (
>sub_acc INTEGER,
>ref_num INTEGER,
>ref_type INTEGER,
>sub_descr CHAR(20),
>PRIMARY KEY (sub_acc, ref_num, ref_type));>
>>alter table sub_accounts add constraint FOREIGN KEY(ref_num, ref_type)
>REFERENCES accounts;
>>select a.tabname, a.tabid, b.constrid from systables a, sysconstraints b
where>a.tabid=b.tabid and a.tabid > 99 and
>a.tabname like '%accounts';
>
>tabname tabid constrid
>
>accounts 123 5
>sub_accounts 124 6
>sub_accounts 124 7
>
>3 row(s) retrieved.
>
>> select * from sysindexes where tabid=124;>
>
>
>idxname 124_6
>owner informix
>tabid 124
>idxtype U
>clustered
>part1 1
>part2 2
>part3 3
>part4 0
>part5 0
>part6 0
>part7 0
>part8 0
>part9 0
>part10 0
>part11 0
>part12 0
>part13 0
>part14 0
>part15 0
>part16 0
>levels
>leaves
>nunique
>clust
>
>idxname 124_7
>owner informix
>tabid 124
>idxtype D
>clustered
>part1 2
>part2 3
>part3 0
>part4 0
>part5 0
>part6 0
>part7 0
>part8 0
>part9 0
>part10 0
>part11 0
>part12 0
>part13 0
>part14 0
>part15 0
>part16 0
>levels
>leaves
>nunique
>clust
>
>2 row(s) retrieved.
>
>DROP TABLE accounts;
>DROP TABLE sub_accounts;>
>CREATE TABLE accounts (
>acc_num INTEGER,
>acc_type INTEGER,
>acc_descr CHAR(20),
>PRIMARY KEY (acc_num, acc_type));>
>CREATE TABLE sub_accounts (
>sub_acc INTEGER,
>ref_num INTEGER ,
>ref_type INTEGER ,
>sub_descr CHAR(20),
>FOREIGN KEY (ref_num, ref_type) REFERENCES accounts (acc_num, acc_type));>
>> select a.tabname, a.tabid, b.constrid from systables a, sysconstraints b
>where a.tabid=b.tabid and a.tabid > 99 and>> a.tabname like '%accounts';
>
>
>tabname tabid constrid
>
>accounts 127 12
>sub_accounts 128 13
>
>2 row(s) retrieved.
>
>> select * from sysindexes where tabid=128;>
>idxname 128_13
>owner informix
>tabid 128
>idxtype D
>clustered
>part1 2
>part2 3
>part3 0
>part4 0
>part5 0
>part6 0
>part7 0
>part8 0
>part9 0
>part10 0
>part11 0
>part12 0
>part13 0
>part14 0
>part15 0
>part16 0
>levels
>leaves
>nunique
>
>Am I mising anything?
>
>I will appreciate it.
>
>Thanks,
>
>Sanjeev K. Sagar
>
>
>"Clifton M. Bean" wrote:
>
>> An index is created in order to allow the constraint to be quickly checked.
>> When you want to remove an element from a primary key, Informix will
>> determine if the value is being used before allowing its deletion.
>>
>> Most often, constraints are used within a test or quality assurance system,
>> to test development code -- nice to get all of those error messages about
>> value not in field. Once the database had undergone those phases, the
>> foreign key constraints are removed.
>>
>> I have rarely seen the need to maintain foreign key constraints on a
>> production system.
>>
>> Clifton Bean
>>
>> "Sanjeev sagar" <sanjeev.sagar@sabre.com> wrote in message
>> news:3944EDFC.E37D41EA@sabre.com...
>> > No. I don't think you understand. I have contacted
>> > Informix.
>> >
>> > Thanks.
>> >
>> > Sanjeev sagar wrote:
>> >
>> > > Mike, Are you sure that you are talking about
>> > Informix Referential
>> > Integrity
>> > > Constraints. Actually your example does not sound
>> > like that.
>> > >
>> > > Informix support Referential Integrity with the help
>> > of Foreign Key which
>> > > will be Primary Key in the child table. That's the
>> > reason Informix create
>> > > the internal index. As per your example It will be
>> > like
>> > >
>> > > Table A
>> > > ---------
>> > > Col1 PK
>> > > Col2 F