The "correct" way is to give your constraints real names. I'm going to
be fixing the FrontBasePlugIn this week to do that so future
migrations won't be so annoying. However, it IS possible to retrieve
the dynamically generated constraint name by navigation the
information_schema tables which contain database metadata. Geert
approved me sending his response to the list when I asked a similar
question just this past week ...
Hello Mike,
Here is an example:
create view check_column_usage as table
definition_schema.check_column_usage;
create view check_constraints as table
definition_schema.check_constraints;
select t4."constraint_name" from
information_schema.tables t0,
information_schema.columns t1,
check_column_usage t2,
check_constraints t3,
information_schema.constraint_names t4
where t0.table_pk = t1.table_pk
and t0."table_name" = 't0'
and t3.check_clause = '"'||'c0'||'" IS NOT NULL' collate
information_schema.case_insensitive
and t1.column_pk = t2.column_pk
and t2.constraint_name_pk = t3.constraint_name_pk
and t3.constraint_name_pk = t4.constraint_names_pk
Assuming that you define the two views "permanently", you could wrap
the above into a stored function:
create function constr_name(:tname VARCHAR(128), :cname
VARCHAR(128)) returns VARCHAR(128)
begin
return
select t4."constraint_name" from
information_schema.tables t0,
information_schema.columns t1,
check_column_usage t2,
check_constraints t3,
information_schema.constraint_names t4
where t0.table_pk = t1.table_pk
and t0."table_name" = :tname
and t3.check_clause = '"'||:cname||'" IS NOT NULL' collate
information_schema.case_insensitive
and t1.column_pk = t2.column_pk
and t2.constraint_name_pk = t3.constraint_name_pk
and t3.constraint_name_pk = t4.constraint_names_pk;
end;
Here is an exampel of a call:
values constr_name('t0', 'c0');
Enjoy :o)
Regards,
Geert
On 20. nov 2007, at 15.32, Mike Schrag wrote:
Is there a way to find and drop a automatically named "not null"
constraint in an generic way? That is, if I know the user/schema/
table/column names, can I find the constraint through
information_schema? I can find the tables entry, the columns
entry, and I even see that there is a constraint in the
constraint_names table, but I can't find the association between
the column and the constraint, and I can't figure out how to tell
that the check constraint is a not null? I see also that there is
an "extract table xxx" command, but that doesn't appear to function
across JDBC.
Thanks!
ms
On Nov 26, 2007, at 5:15 AM, Timo Hoepfner wrote:
Hi,
does anyone know a painless way to rename NOT NULL columns in
FrontBase?
Problem is, that FB creates column constraints with unpredictable
names (which can differ on different machines) when creating NOT
NULL columns. These need to be dropped before doing the actual
rename of the column (and recreated afterwards).
I want to use it with migrations, so using FBManager is not an option.
Timo
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/mschrag%40mdimension.com
This email sent to [EMAIL PROTECTED]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [EMAIL PROTECTED]
