How to modify the multi-column primary key of a table?

Omari Stephens <[email protected]>
Newsgroups gmane.comp.java.hsqldb.user
Message-ID <CAA8ki2tNKRsk0vgbmAVw0UnG2vsNCEb56HAWm4ov-=5Di9u86Q@mail.gmail.com>
Howdy, y'all

(note: this is a simplification of my actual problem)

So, I have a table PEOPLE with columns (name, email, phone):
CREATE TABLE PEOPLE (
 name VARCHAR(10),
 email VARCHAR(10),
 phone VARCHAR(10),
 PRIMARY KEY (alpha, bravo, charlie)
);

I also have a table SIBLINGS, with some other columns, and which uses
the columns from KEY as a set of foreign keys
CREATE TABLE SIBLINGS (
 name VARCHAR(10),
 email VARCHAR(10),
 phone VARCHAR(10),
 sibling VARCHAR(10) NOT NULL,
 relation VARCHAR(10) NOT NULL,
FOREIGN KEY (name, email, phone) REFERENCES PEOPLE ON UPDATE CASCADE
ON DELETE CASCADE
);


I now discover that I want to add a new field to the KEY table.  My
question is, how do I ALTER those tables so that the end state is as
if I had specified the extra column at the outset?  Actually adding
the columns is straightforward:
ALTER TABLE KEY ADD COLUMN address VARCHAR(10) DEFAULT '(unspecified)';
ALTER TABLE SIBLINGS ADD COLUMN address VARCHAR(10) DEFAULT '(unspecified)';

Q1: The tricky part is managing the constraints.  As far as I
understand, "PRIMARY KEY" translates to multi-column "UNIQUE" and "NOT
NULL" constraints, and also creates an index on that set of columns.
So it seems like the first thing I need to do is to drop that pair of
constraints on PEOPLE and add new constraints that apply across (name,
email, phone, address).  How do I do that?

Q2: How I should ALTER SIBLINGS to maintain consistency.  In
particular, will anything untoward happen if I try to drop the
existing FOREIGN KEY constraint before adding the new one?  (I am
guaranteed to be the only writer to the DB during this upgrade)

Q3: Finally, and more generally, how do I drop unnamed table
constraints?  I know that hsqldb gives me:
ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>;
But where do I get the <constraintname> from?


Thanks!
--xsdg

------------------------------------------------------------------------------
Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester  
Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the  
endpoint security space. For insight on selecting the right partner to 
tackle endpoint security challenges, access the full report. 
http://p.sf.net/sfu/symantec-dev2dev
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.