References Unique Index

From: Avnish Rastogi DTA <avnish_at_TULNEPZ.UNISYS.COM>
Date: Fri, 2 Feb 1996 12:51:20 +0530
Message-Id: <9602020700.AA27056@alice.jcc.com>

How Unique Constraint works. i created following tables

create table test1
(

    cd_mst_code_Cvndr varchar2(4) not null,

    pcar_vndr_tag       char(1)     not null,
                    check (pcar_vndr_tag in ('P','C','G')),
    cd_mst_code_org     varchar2(4),

    cd_mst_code_dest varchar2(4),
    pcar_vndr_priorty number(3),

    cd_mst_code_cls     varchar2(4),
    pcar_corp_id        varchar2(10),
    pcar_vndr_phone     varchar2(12),
    constraint          test1_idx_1

    unique (cd_mst_code_Cvndr,cd_mst_code_org,cd_mst_code_dest) )
/
~
create table test2
(

    cd_mst_code_Cvndr   varchar2(4) not null,
    cd_mst_code_ne      varchar2(4) not null,
    pcar_ne_tag         char(1)     not null,
                        check (pcar_ne_tag in ('G','C','P'))
    cd_mst_code_org     varchar2(4),

    cd_mst_code_dest varchar2(4),
    pcar_vndr_priorty number(3),
    constraint fk_test2_1 foreign key
    (cd_mst_code_cvndr,cd_mst_code_org,cd_mst_code_dest)     references test1
    (cd_mst_code_cvndr,cd_mst_code_org,cd_mst_code_dest) )
/

i inserted follwing values in table test1

(cd_mst_code_Cvndr,pcar_vndr_tag) values ('AB','G')
(cd_mst_code_Cvndr,pcar_vndr_tag,cd_mst_code_org) values ('AC','C','DEL')
(cd_mst_code_Cvndr,pcar_vndr_tag,cd_mst_code_org,cd_mst_code_dest)
values ('AD','P','DEL','BOM')

 if i insert following values in table test2

(cd_mst_code_cvndr,cd_mst_code_ne,pcar_ne_tag) values ('AD,'G','NE1');

in this case in parent table 'AD' exist but with Orihn and destination where as i am inserting origin and destination as null

is this behaviour ok.

                                        thanks in advance


                                                                avnish

Received on Fri Feb 02 1996 - 02:00:50 CST