|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
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 !
|
|
#2
December 13th, 2006, 09:04 AM
|
||||
|
||||
|
Direct modifications of metadata tables are one of the best ways to get into trouble
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
#3
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 
|
|
#4
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:
Code:
SELECT r.rdb$constraint_name
FROM rdb$relation_constraints r,
rdb$check_constraints c
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';
Code:
ALTER TABLE toccupation DROP CONSTRAINT INTEG_7; COMMIT;
|
|
#6
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.
Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com
__________________
Martijn Tonies Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle Upscene Productions http://www.upscene.com
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Remove NOT NULL on a column |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
