SQL Contraints

From: Patrick Lanphier <planphier_at_psu.edu>
Date: Tue, 18 May 1999 12:03:22 -0400
Message-ID: <37418F4A.E6537077@psu.edu>

I am wondering the best way to solve this problem. I would like to do the following generate a unique id between account.account_number and groups.group_id that would then be used to join in one migrated foreign key to addresses.lookup_id. Would the below SQL work properly? If so do you know how to get ERwin to do this?

CREATE TABLE addresses (

       lookup_id            INTEGER NOT NULL,
       address_type         VARCHAR2(16) NOT NULL,
       address1             VARCHAR2(40) NOT NULL,
       address2             VARCHAR2(40) NULL,
       address3             VARCHAR2(40) NULL,
       city                 VARCHAR2(25) NOT NULL,
       state                VARCHAR2(2) NULL,
       country              VARCHAR2(20) NULL,
       postal_code          VARCHAR2(20) NULL,
       start_date           DATE NOT NULL,
       end_date             DATE NULL,
       last_update_date     DATE NOT NULL,
       last_update_by       VARCHAR2(24) NOT NULL,
       created_by           VARCHAR2(24) NOT NULL,
       creation_date        DATE NOT NULL,
       PRIMARY KEY (lookup_id), 
       FOREIGN KEY (lookup_id) REFERENCES account(account_number), 
       FOREIGN KEY (lookup_id) REFERENCES groups(group_id),
       FOREIGN KEY (address_type) REFERENCES address_type

);

CREATE TABLE account (

       account_number       INTEGER DEFAULT ADDRESSES.nextval NOT NULL,
       semester_code        VARCHAR2(8) NOT NULL,
       title                VARCHAR2(64) NOT NULL,
       phone_id             VARCHAR2(16) NULL,
       phone_id             NUMBER NULL,
       account_type         CHAR(3) NOT NULL,
       expire_date          DATE NULL,
       overdate             DATE NULL,
       opendate             DATE NULL,
       acctstat             CHAR(2) NOT NULL,
       sign_date            DATE NULL,
       moveaccounttype      CHAR(3) NOT NULL,
       moveaccountnumber    INTEGER NOT NULL,
       overdrawwho          VARCHAR2(16) NOT NULL,
       overdrawhow          VARCHAR2(16) NOT NULL,
       overdrawworkwhen     VARCHAR2(16) NOT NULL,
       overdrawuni          VARCHAR2(16) NOT NULL,
       autoonly             VARCHAR2(16) NOT NULL,
       group_code_list      VARCHAR2(16) NOT NULL,
       unit_list            VARCHAR2(16) NOT NULL,
       default_days         VARCHAR2(16) NOT NULL,
       max_days             VARCHAR2(16) NOT NULL,
       num_warnings         VARCHAR2(16) NOT NULL,
       last_update_date     DATE NOT NULL,
       lock_acccount        VARCHAR2(16) NOT NULL,
       account_balance      VARCHAR2(16) NOT NULL,
       last_update_by       NUMBER(15) NOT NULL,
       created_by           NUMBER(15) NOT NULL,
       creation_date        DATE NOT NULL,
       PRIMARY KEY (account_number), 
       FOREIGN KEY (phone_id) REFERENCES phones, 
       FOREIGN KEY (phone_id) REFERENCES personal, 
       FOREIGN KEY (semester_code) REFERENCES semesters

);

CREATE TABLE groups (

       group_id             INTEGER DEFAULT ADDRESSES.nextval NOT NULL,
       group_name           VARCHAR2(16) NOT NULL,
       phone_id             VARCHAR2(16) NULL,
       supervisor           NUMBER NULL,
       bill_type            VARCHAR2(16) NULL,
       last_update_date     DATE NOT NULL,
       start_date           DATE NOT NULL,
       last_update_by       VARCHAR2(24) NOT NULL,
       end_date             DATE NULL,
       created_by           VARCHAR2(24) NOT NULL,
       creation_date        DATE NOT NULL,
       group_description    VARCHAR2(40) NOT NULL,
       PRIMARY KEY (group_id), 
       FOREIGN KEY (phone_id) REFERENCES phones, 
       FOREIGN KEY (supervisor) REFERENCES personal, 
       FOREIGN KEY (bill_type) REFERENCES bill_type

);

Patrick Lanphier
Advanced Information Technologies
The Pennsylvania State University Received on Tue May 18 1999 - 11:03:22 CDT