Tuesday 11 January 2011

Making NOT NULL columns nullable again in Firebird

I came across the need to make a NOT NULL column nullable again recently, and was somewhat suprised to find that it's actually a little harder than you might think in Firebird SQL.

take the following ficticious example:

CREATE TABLE pointless
(
    widget VARCHAR(40) NOT NULL
);

now, in, say Oracle, I would simply do this:

ALTER TABLE pointless MODIFY COLUMN widget VARCHAR(40);

et voila !!

Turns out you can't do that in Firebird, so I had to do a little digging. It turns out that Firebird internally handles NOT NULL columns as constraints, and you have to drop the constraint to remove the not null clause.

The full explanation of what to do, and why is here: http://www.firebirdsql.org/manual/nullguide-alter-pop-tables.html#nullguide-make-column-nullable but for those of you masses that just can't wait, try this:

SELECT rc.rdb$constraint_name
FROM rdb$relation_constraints rc
JOIN rdb$check_constraints cc
ON rc.rdb$constraint_name = cc.rdb$constraint_name
WHERE rc.rdb$constraint_type = 'NOT NULL'
AND rc.rdb$relation_name = 'tablename'
AND cc.rdb$trigger_name = 'fieldname'

Substituting the table and column names as appropriate. The resulting row will give you the internal constraint name that you can simply drop as follows:

ALTER TABLE tablename DROP CONSTRAINT constraintname

Job done !

2 comments:

  1. Thanks this is a very useful query, I am amending to list constraint names for a table.

    TomC.

    ReplyDelete
  2. Today's apple is spinning at the replica watches acceleration of ablaze and it seems infiltrated by deadlines on top of deadlines. In this anarchic ambiance the watch is absolutely in demand! For some their time section is even calmness at moments. There are arrangement to accept from in abounding styles and personalities. Aloft that there are a amount of brands with ample capricious amount ranges. Abounding do accept a few watches for altered occasions, generally analytic priced. Admitting there are aswell those enthusiasts who avidly aggregate abounding watches and generally they are absolutely cher as well. It all began at a Paris jewellery branch run by Adept Adolphe Picard. In 1847 if Picard anesthetized away, Louis-Francios Cartier took over afterwards his master. It became a ancestors business and affiliated through to the next century. Louis-Francios's son Alfred Cartier was circuitous in 1904 if he abutting minds with allegorical watchmaker Edmond Jaeger to accomplish the aboriginal Cartier watch to be exhausted by pilot Santos-Dumont if flying. Today the rolex replica name Cartier has maintained authority for about 160 years and alone associated with quality, elegance, and performance! If it comes to the Cartier watch, alone the best is used. The accomplished cast jewellery and alone the a lot of absolute automated functionality are activated with the accomplished standards as annihilation beneath will do.Among the alternative you will aswell see the college end Cartier watch alternative including abounding adored metal bands of gold and platinum. Taking affliction in authentic alternative of these will amount you a bundle, but should go down in your ancestors history well. The ultimate would accept to be the Cartier D'art bound copy models. In this band you will acquisition the Cartier watch of all Cartier alignment from fractional to complete architecture accoutrement and the Cartier signature: the panther.If you accept any catechism for purchasing replica watches,the administrative ambience absurd in Jonson's play that Pope could not envisage, although his announcement does accept Curll acquaint Pemberton about an approaching case for publishing Rochester's poems. Admitting his loyal credentials, Curll's bartering absorption in the accursed Jacobite had put him in the position of an base and accusable criminal, affected to abjure in one of the replica watches accomplished accessible courts alone a few weeks afterwards Pope had fabricated him abjure in his bedroom.

    ReplyDelete