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

bump?

--xsdg

On Fri, Mar 8, 2013 at 5:11 PM, Omari Stephens <[email protected]> wrote:
> 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