RE: REFERENCING TABLES, AND USING A ALPHABETIC INTEGRITY CONSTRAINT

From: <vcowans_at_cougarnet.netexp.net>
Date: 1997/08/16
Message-ID: <33F60A51.4620@cougarnet.netexp.net>#1/1

MASTER CUSTOMER FILE
drop table VDCMCF;
create table vdcmcf(

        MCF_CNUM        char(6),
        MCF_CNAME       char(25),
        MCF_CATTN       char(25),
        MCF_STREET      char(25),
        MCF_CITY        char(15),
        MCF_STATE       char(2),
        MCF_ZIP         char(5),
        MCF_CRATE       char(1),
        MCF_CLIMIT      numeric(7,2),
        MCF_CBAL        numeric(8,2),
        MCF_CYTD        numeric(8,2),
        CHECK (mcf_state IN
       
('AL','AK','AZ','AR','CA','CO','CT','DE','DC','FL','GA','HI','ID',
          


'IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO',

          


'MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA',

          


'PR','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY')),

        CHECK (mcf_zip between '00000' and '99999'),
        CHECK (mcf_crate IN ('T','F','Y','N')),
        CHECK (mcf_climit < 5000.00),
        CHECK (mcf_cbal between 0 and 999999.99),
        CHECK (mcf_cytd >= 0.00));
desc vdcmcf;
			MASTER INVENTORY FILE

drop table VDCMIF;
create table vdcmif(

        MIF_PNUM        numeric(6),
        MIF_DESC        char(25),
        MIF_QONHAND     numeric(4),
        MIF_QORDER      numeric(4),
        MIF_ORDERPT     numeric(4),
        MIF_ORDQUA      numeric(4),
        MIF_PRICE       numeric(8,2),
        MIF_COST        numeric(8,2),
        MIF_BKORDER     numeric(4),
        MIF_CLASS       char(1)
        );
desc vdcmif;
			MASTER ORDER FILE

drop table VDCMOF;
create table vdcmof(

        MOF_ORDNUMB     char(6),
        MOF_CNUM        char(6),
        MOF_DATE        date,
        MOF_PNUM        numeric(6),
        MOF_ORDAMT      numeric(5)
        );
desc from vdcmof;
			MASTER PAYMENT FILE

drop table vdcmpf;
create table vdcmpf(

        MPF_PCNUM       char(6),
        MPF_PDATE       date,
        MPF_PCHKNUM     char(10),
        MPF_PAMOUNT     numeric(8,2)
        );

desc vdcmpf;

QUESTION: HOW DO I GO ABOUT CHECKING TO SEE IF MCF_CNAME IS ALPHABETIC? QUESTION: HOW DO I REFERENCE ANOTHER TABLE? I HAVE FOUR TABLES MASTER CUSTOMER FILE, MASTER INVENTORY FILE, MASTER PAYMENT FILE, AND THE MASTER ORDER FILE. IN THE MASTER PAYMENT FILE MY MPF_CNUM NEEDS TO REFERENCE THE MCF_CNUM IN THE MASTER CUSTOMER FILE, AND IN THE MASTER ORDER FILE, MY MOF_CNUM NEEDS TO REFERENCE MCF_CNUM WITHIN THE MASTER CUSTOMER FILE, ALSO WITHIN THAT SAME TABLE THE MOF_PNUM NEEDS TO REFERENCE MIF_PNUM WITHIN THE MASTER INVENTORY FILE. CAN ANYONE OUT THERE HELP ME WITH THIS PROBLEM? IF YOU CAN PLEASE E-MAIL ME AND LET ME KNOW HOW TO GO ABOUT DOING THIS. THANKS Received on Sat Aug 16 1997 - 00:00:00 CDT