person wrote:
I need to drop the primary key constraint on a table which was created something like this:
CREATE TABLE NameValue (
Name VARCHAR(100) NOT NULL,
Value VARCHAR(100)
);
ALTER TABLE NameValue ADD PRIMARY KEY (Name);
I.e. the constraint does not have name. What is the best way to drop the constraint in a script? From reading a similar question in this group it looks like
delete from
RDB$RELATION_CONSTRAINTS
where
RDB$CONSTRAINT_TYPE='PRIMARY KEY' and
RDB$RELATION_NAME='NameValue'
should do the trick. Can someone please confirm this as a number of our customers will be affected.
Helen Borrie answers:
No, it's never a good idea to manipulate metadata by performing your own DML operations on the system tables. That's in the top 5 ways to corrupt databases. READ them, don't change them!
The "no-name" constraints in fact do have unique names. You can query RDB$Relation_Constraints with your search clause to find out what they are.
select rdb$constraint_name from rdb$relation_constraints
where
RDB$CONSTRAINT_TYPE='PRIMARY KEY' and
RDB$RELATION_NAME='NameValue'
Then, when you know it, you can drop it.
alter table Department
drop constraint INTEG_13