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

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Omari Stephens-4
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
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Omari Stephens-4
bump?

--xsdg

On Fri, Mar 8, 2013 at 5:11 PM, Omari Stephens <[hidden email]> 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
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...