2 messages in com.omnigroup.webobjects-devRe: Proper SQL to alter column on Fro...| From | Sent On | Attachments |
|---|---|---|
| MacFirst | Mar 26, 2004 3:32 pm | |
| Chuck Hill | Mar 27, 2004 10:00 am |
| Subject: | Re: Proper SQL to alter column on Frontbase | |
|---|---|---|
| From: | Chuck Hill (chi...@global-village.net) | |
| Date: | Mar 27, 2004 10:00:31 am | |
| List: | com.omnigroup.webobjects-dev | |
Remove the check, alter the table, and reapply the check. I've culled these notes from the list. They may be of help:
Q. I need to remove a ForeignKey constraint on a table. To do this I am going to use: ALTER TABLE table DROP CONSTRAINT constraint RESTRICT; How do I get the name of a CONSTRAINT with fb?
A. You should be able to look it up in FBWebManager or FBManager. Both tools should also be able to drop the constaint. Failing that, do something in sql92 that voilates the constraint and note the name used in the exception. It will be something like _C012345.
A. You can get the constraint name using the "information_schema": SELECT "CONSTRAINT_NAME","CONSTRAINT_TYPE" FROM "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS", "INFORMATION_SCHEMA"."CONSTRAINT_NAMES", "INFORMATION_SCHEMA"."TABLES" WHERE "INFORMATION_SCHEMA"."CONSTRAINT_NAMES"."CONSTRAINT_NAMES_PK" = "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS"."CONSTRAINT_NAME_PK" AND "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS"."TABLE_PK" = "INFORMATION_SCHEMA"."TABLES"."TABLE_PK" AND "INFORMATION_SCHEMA"."TABLES"."TABLE_NAME" LIKE 'YOUR_TABLE_NAME';
A. Do SHOW TABLE <table> FULL and look for the appropriate constraint. Look for things like this:
FOREIGN KEY NAME = _C0000000764 <- this is the name DEFERRABLE = NO CHECK TIME = INITIALLY IMMEDIATE COLUMNS XXXXXXXXX <- column in this table MATCH OPTION = NONE UPDATE RULE = NO ACTION DELETE RULE = NO ACTION REFD_TABLE = XXXXXXX <- referenced table REFD_SCHEMA = XXXXXXXX <- your schema REFD_NAME = _C0000000102 REFD_COLUMNS XXXXXXXX <- column in referenced table
Chuck
On Mar 26, 2004, at 3:32 PM, MacFirst wrote:
Frontbase says they're SQL92 compliant (and I believe them), but I can't quite seem to get the correct SQL to modify my database. I've got a VARCHAR field that's 256 long that I want to change to be 4095 long. Here's what I get:
tedsdb@localhost#22> show table MF_ONE_OFFS;
-- -- TABLE: TEDSDB._SYSTEM.MF_ONE_OFFS CREATE TABLE MF_ONE_OFFS ( X_DATA VARCHAR (256) NOT NULL , NAME VARCHAR (256) NOT NULL , ONE_OFF_ID INT NOT NULL , PRIMARY KEY (ONE_OFF_ID) );
tedsdb@localhost#23> alter column "MF_ONE_OFFS"."X_DATA" to varchar(4095);
Semantic error 278. Column - X_DATA - is referenced in one or more VIEWs (or constraints). Semantic error 485. Near: ALTER COLUMN "MF_ONE_OFFS"."X_DATA" TO VARCHAR(4095);. Exception 363. Transaction rollback.
I BELIEVE that the problem is that X_DATA is not-null, and I have to somehow include that in my alter-column (or maybe remove the check, then add it back in, later?)
Either way, I can't seem to get just the right syntax to make the chance.
Help?
Thanks!
_______________________________________________ WebObjects-dev mailing list WebO...@omnigroup.com http://www.omnigroup.com/mailman/listinfo/webobjects-dev