Hello:
listed below is the order for the creation of tables and execution of ALTER TABLE resources in an Apache Derby Embedded db;
Note the error at: alterInstructor.
I create the instructor table then the booking table. The instructor table has book_num FK.
After the booking table is created I run the script alterTable to alter the instructor table.
Note: the alterPayee script runs fine but the book_num is a different data type. Is this a problem?
It creates the database but it says there is an error when it tries to alter the instructor table.
Can anyone see why this is?
run:
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in bCreatedTables:
created table: createAdminTable
created table: createPayeeTable
created table: createStudentTable
created table: alterPayee
created table: createInstructorTable
created table: createBookingTable
[b]
Error at : alterInstructor
ALTER TABLE instructor ADD CONSTRAINT BOOK_NUM_FK foreign key(book_num) REFERENCES booking(book_num)
[/b]
created table: createInstrAvailableTable
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
created table: createPayrollTable
created table: createUserTable
created table: createUserGroupMappingTable
created table: createUserGroupTable
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
db and tables created:
inserted data int: insertAdminTable
inserted data int: insertPayeeTable
inserted data int: insertStudentTable
inserted data int: insertInstructorTable
inserted data int: insertPaymentTable
inserted data int: insertUserTable
inserted data int: insertUserGroupTable
inserted data int: insertUserGroupMappingTable
shutdown database
disconnected after building the database java.sql.SQLNonTransientConnectionException: Database 'schoolofdb' shutdown.
disconnected after building the database
createInstructorTable
<entry key="createInstructorTable"> CREATE TABLE instructor(
instr_uid VARCHAR(11) PRIMARY KEY,
instr_password VARCHAR(11),
instr_lname VARCHAR(20),
instr_mname VARCHAR(20),
instr_fname VARCHAR(20),
instr_gender VARCHAR(1),
instr_start_date DATE,
instr_end_date DATE,
instr_dob DATE,
instr_address VARCHAR(20),
instr_state VARCHAR(2),
instr_zip VARCHAR(5),
instr_area_code VARCHAR(3),
instr_phone VARCHAR(10),
instr_location VARCHAR (11),
instr_rate DECIMAL(5,2),
instr_pay_rate DECIMAL(5,2),
[b]book_num SMALLINT,[/b]
instr_email VARCHAR(90)
) </entry>
createBookingTable
<entry key="createBookingTable"> CREATE TABLE booking(
[b] book_num SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1,INCREMENT BY 1),[/b]
book_instrument_type VARCHAR(11),
book_start_time TIME,
book_end_time TIME,
book_date DATE,
book_location VARCHAR(11),
book_status VARCHAR(6),
book_cancel_date DATE,
book_refund_memo SMALLINT,
stu_uid VARCHAR(11),
instr_uid VARCHAR(11),
payment_num INT
) </entry>