Kbase 21630: Table Definition Not Complete (7675) - SQL Table Constraints
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/2007 |
|
Status: Verified
SYMPTOM(s):
Getting error 7675 and 8926 when creating tables
Creating Tables via SQL-92
The following SQL-92 statements is used to create two tables. The 1st table is referenced from the 2nd table using the "state" column.
CREATE TABLE PUB.states (state VARCHAR (2) NOT NULL PRIMARY KEY,
state_full VARCHAR (20) NOT NULL);
COMMIT WORK;
CREATE TABLE PUB.mechanicals (
mech_no INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address_1 VARCHAR(50),
address_2 VARCHAR(50),
address_3 VARCHAR(50),
city VARCHAR(20) NOT NULL,
state VARCHAR(2) NOT NULL REFERENCES states (state),
zip_code VARCHAR(10),
phone VARCHAR(18),
fax VARCHAR(18),
email_general VARCHAR(50),
contact_1 VARCHAR(40),
contact_1_email VARCHAR(50),
contact_2 VARCHAR(40),
contact_2_email VARCHAR(50),
contact_3 VARCHAR(40),
contact_3_email VARCHAR(50),
special_intstructions VARCHAR(1000));
COMMIT WORK;
Upon initial execution of these SQL statements error 8926 is displayed
Statements: <number>; Updates <number>; Rows <number>; Errors: <number>; Warnings: <number>. (8926)
Statements: 4; Updates 0; Rows 0; Errors: 0; Warnings: 0. (8926)
However, if the 1st table is NOT referenced from the 2nd table correctly you may encounter other error messages when you attempt to use an INSERT statement. For example:
INSERT INTO PUB.mechanicals (mech_no, name, address_1, address_2,
address_3, city, state, zip_code, phone, fax, email_general, contact_1, contact_1_email, contact_2, contact_2_email, contact_3, contact_3_email, special_intstructions)
VALUES (1, 'Joe Mechanic', '14 Oak Park','14 Oak Park'.'14 Oak
Park','Bedford','MA'.'01730','781-280-4543','email@email.com','TSE1','emial@email.com','TSE2','email@email.com','TSE3','email@email.com','This doesn't work properly !');
COMMIT WORK;
Table definition not complete (7675)
SQLState=HY000
ErrorCode=-20117
[JDBC Progress Driver]:Table definition not complete (7675)
CAUSE:
When a table is referenced from within another table the default qualification is the current schema. Because the other table was created in schema PUB, it has to be explicit. This error means that when performing the insert, there were table constraints that had to be checked. However, some constraint in the table definition (of the receiving table) references some other table not yet created. Here, SQL thought the other table was not defined because the rules of qualification "pointed to" a non-existent table. The table definition can be checked out for incomplete status by looking at the column systables.tbl_status. For example:
select tbl, tbl_status from sysprogress.systables;
If tbl_status = 'N', then the table is not yet fully defined because of a constraint problem.
FIX:
This line:
state VARCHAR(2) NOT NULL REFERENCES states (state),
MUST be changed to:
state VARCHAR(2) NOT NULL REFERENCES pub.states (state),