December 13th, 2006, 04:38 AM
Remove NOT NULL on a column
Hi All !
I have a problem.
First of all, I have a table called TOCCUPATION
In this table, I've got a field called STRUCTUREPARETAPE
This field is NOT NULL (and it is a foreign key that references the table called TSTRUCTUREPARETAPE)
Now I change this field and I remove the NOT NULL by doing this :
update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'STRUCTUREPARETAPE') and
(RDB$RELATION_NAME = 'TPOSTEPARETAPE')
Unfortunately, I can't do an insert with a null value for this field.
I tried to create a new domain for the STRUCTUREPARETAPE field but it doesn't work.
Some idea ?
Thank you !
December 13th, 2006, 09:04 AM
Direct modifications of metadata tables are one of the best ways to get into trouble
December 13th, 2006, 03:30 PM
Sure but I need this modification.
This is the way I proceed and it's not secure at all because you have to know what you do but everything seems alright.
I dropped every constraints and procedures
I copied the data of the column in a temporary table
I dropped the column
I recreate the column without the not null constraint
I copied data from the temp table to the official table
I recreate every constraint and procedures
It is 'du bidouillage' as we say here, in belgium but that was the only way I find.
Hope to avoid troubles
December 15th, 2006, 04:30 PM
You can drop the constraint that is connected to the NOT NULL definition. To find out the constraint name, use the following query:
Then use the name that is returned from that query to drop the constraint. Assuming the constraint is named INTEG_7:
FROM rdb$relation_constraints r,
WHERE r.rdb$constraint_name = c.rdb$constraint_name
AND r.rdb$relation_name = 'TOCCUPATION'
AND c.rdb$trigger_name = 'STRUCTUREPARETAPE'
AND r.rdb$constraint_type = 'NOT NULL';
ALTER TABLE toccupation DROP CONSTRAINT INTEG_7;
December 18th, 2006, 08:05 AM
ok, thank you
I'll try this way.
December 23rd, 2006, 06:00 AM
Shammat, I don't think that's all. You actually need to modify the RDB$NULL_FLAG on RDB$FIELDS as well.
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server
Database development questions? Check the forum!
Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle