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