|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
May 10th, 2005, 02:38 PM
|
|||
|
|||
how to alter primary key column?
Hi!
I’ve got problems to alter a column witch is a primary key. I want to change a column from VARCHAR(50) to VARCHAR(100) This works only with non primary key columns: ALTER TABLE table ALTER COLUMN column TYPE VARCHAR(100) Can someone give me a example to do that? Thanks! Torsten
|
|
#2
May 11th, 2005, 02:39 AM
|
||||
|
||||
well ... first of all ... i think you should consider using an integer primary key.
Code:
CREATE TABLE ..... (ID INTEGER NOT NULL PRIMARY KEY ...........) anyway ... to modify the primary key you must drop the constraints related to the primary key (not null, primary key) drop the index (RDB$PRIMARY...) and then you are left with a normal column which you can alter the way you want it.
__________________
If i've been helpful, please add to my reputation. My unfinished site: http://www.dever.ro
|
|
#3
May 11th, 2005, 11:32 AM
|
|||
|
|||
Thanks for your answer
But I’m not able to drop column constraints! I’ve tried a lot of things – for example this: ALTER TABLE table ALTER COLUMN column DROP CONSTRAINT PRIMARY KEY But nothing worked!
|
|
#4
May 12th, 2005, 01:10 AM
|
||||
|
||||
|
The correct sql would be something like:
(this is a real example) (the primary key constraint) Code:
ALTER TABLE TEST DROP CONSTRAINT INTEG_23 (the not null constraint) Code:
ALTER TABLE TEST DROP CONSTRAINT INTEG_18 after that you can alter your column: Code:
ALTER TABLE table ALTER column ....
|
|
#5
May 12th, 2005, 02:31 PM
|
|||
|
|||
Thanks again!
But the problem is – the constraint changes always. In my case, it’s INTEG_47! Now I’ve found a way to delete a PRIMARY KEY. But it takes two steps: select rdb$constraint_name from rdb$relation_constraints where rdb$constraint_type='PRIMARY KEY' and rdb$relation_name='tablename'; ALTER TABLE tablename DROP CONSTRAINT <result of selection>; Why isn’t it possible, just to say DROP PRIMARY KEY ? Sincerely Torsten
|
|
#6
May 12th, 2005, 03:49 PM
|
|||
|
|||
|
Why don't you simply give your primary key a name?
Code:
CREATE TABLE foo ( id integer ... ); ALTER TABLE foo ADD CONSTRAINT foo_pk PRIMARY KEY (id);
|
|
#7
May 13th, 2005, 03:27 AM
|
||||
|
||||
|
Quote:
In my opinion you should have no reasons to delete a PRIMARY KEY especially when that is a FOREIGN KEY in another table and it is set by a GENERATOR ! What you have now is a perfect example of a bad database design in the first place 
|
|
#8
May 16th, 2005, 07:19 AM
|
|||
|
|||
|
Quote:
@SilverDB: Haha!  Please look at the top of the thread. I don't want to delete the primary key at all! @shammat: Thanks! I will try that!
|
|
#9
July 30th, 2008, 11:03 PM
|
|||
|
|||
|
Erase Rdb$relation Constraints Failed Action Cancenceld By Trigger (1)
Quote:
Hi! Im trying to drop the constraints of my primarykey but the error ERASE RDB$RELATION CONSTRAINTS FAILED ACTION CANCENCELD BY TRIGGER (1) TO PRESERVE DATA INTEGRITY I tried to look for the trigger in the other tables and but i cant find any that uses my primary key (ID_NO). Where can i find the trigger? Please help...
|
|
#10
August 1st, 2008, 04:46 PM
|
|||
|
|||
|
Quote:
It is not thee trigger you should be looking for. It is the Foreign Key constraint linked to your PK.
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > how to alter primary key column? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
