Alter tables and foreign keys

[ceyesuma]

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>

[ceyesuma]

Should these tables be altered in on location after all the tables have been created?

[tesuji]

...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....

Well, you are showing that much sql code except the most important script which do the ALTER TABLE ....

You should consider:

1. All your table should have primary keys, at least the table where a foreign key is referencing to must have one. That is book_num must be primary key of booking table.

2. The order of ALTER statements is important: The object a reference points to must already exist. So the order should be: Alter table booking ADD primary key... ; Alter table instructor ADD foreign key ... ;

3. Whether different datatypes are significant or not depends on whether the database is able to do the converting automatically. If not possible, you need an cast() explicitly. If the datatypes are too different, also cast() won't work. You should read and understand derby manual about the compatibility and (automatical) typecasting of DTs.

4. If you have inserted some data before defining foreign keys, sometimes this is impossible depending on already existing constraints (e.g. NOT NULL etc). Also order is here important: a book_num referenced in instructor table by foreign key must already exists in booking table.

5. Derby, JavaDB, Cloudscape they all also understand conventional definition of primary and foreign key constraints:

-- table x must be created first!
create table x(a int not null, b char, primary key (a));

-- now table x can be referenced in table y:
create table y(c int not null, d char, e int,
  primary key (c), foreign key (e) references x on delete cascade);

btw, it would be really simpler to help you if you also post the alter statements!

btw2, which column of instructor table should reference to table booking (book_num) ? Furthermore, which column of table booking is that book_num? your create table statements do not contain that information at all !

you can also look at your older posting.

-- tesu