sql10.txt

客户信息管理系统

/* Table: COUNTRY, Owner: MISDBA */
CREATE TABLE COUNTRY (CODE CHAR(3) NOT NULL,
        NAME VARCHAR(50) NOT NULL,
CONSTRAINT COUNTRY_CODE PRIMARY KEY (CODE));

/* Table: PROVINCE, Owner: MISDBA */
CREATE TABLE PROVINCE (CODE CHAR(5) NOT NULL,
        NAME VARCHAR(50) NOT NULL,
CONSTRAINT PROVINCE_CODE PRIMARY KEY (CODE));

/* Table: CITY, Owner: MISDBA */
CREATE TABLE CITY (CODE CHAR(5) NOT NULL,
        NAME VARCHAR(50) NOT NULL,
CONSTRAINT CITY_CODE PRIMARY KEY (CODE));

/* Table: CUSTOM, Owner: MISDBA */
CREATE TABLE CUSTOM (ID CHAR(10) NOT NULL,
        NAME VARCHAR(20),
        COMP_NAME VARCHAR(50),
        COMP_ADDR VARCHAR(100),
        CITY CHAR(5) NOT NULL,
        PROVINCE CHAR(5) NOT NULL,
        COUNTRY CHAR(3) NOT NULL,
        ZIP VARCHAR(10),
        TEL VARCHAR(20),
        FAX VARCHAR(20),
        EMAIL VARCHAR(20),
        REMARK VARCHAR(400),
CONSTRAINT CUSTOM_ID PRIMARY KEY (ID));

/*  ForeignKey CITY  */
ALTER TABLE CUSTOM
  ADD CONSTRAINT CITY 
  FOREIGN KEY (CITY) 
  REFERENCES CITY;
/*  ForeignKey COUN  */
ALTER TABLE CUSTOM
  ADD CONSTRAINT COUN 
  FOREIGN KEY (COUNTRY) 
  REFERENCES COUNTRY;
/*  ForeignKey PROV  */
ALTER TABLE CUSTOM
  ADD CONSTRAINT PROV 
  FOREIGN KEY (PROVINCE) 
  REFERENCES PROVINCE;