Re: SQL Contraints

Patrick,

From your schema I assume that you intend all three table to be populated in the same transaction. I would suggest that you use a stored procedure to insert into the tables in one operation. You get the value of addresses.nextval and simply use it as the PK of the two dependant tables. Otherwise if you are using Oracle8 you could make an object view combining the three tables and create an 'instead of' trigger to get the next sequence value and assign it to the three PK's.

HTH, David.

Patrick Lanphier wrote:

> 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 - 19:14:17 CDT