Обсуждение: How to change primary key in a table

I have the following table:

CREATE TABLE penalty_codes (
    penalty_code varchar(10),
    penalty_name varchar(32),
    penalty_name_sv varchar(40),
    penalty_id serial PRIMARY KEY
);

which I have been using for a year or two. Today I realized that the
id-column being a primary key is really not useful, while the
code-column is instead. Three other tables refer on the id-values so
the column can certainly not be dropped, but is it possible to change
the primary key to the code-column without breaking things?

I tried this:

========================================
SQL error:
ERROR:  cannot drop constraint penalty_codes_pkey on table
penalty_codes because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

In statement:
ALTER TABLE "penalty_codes" DROP CONSTRAINT "penalty_codes_pkey"
========================================

I'm aware of what CASCADE does when you drop a table for instance, but
I have no idea what happens if you cascade drop a primary key.

How can I switch the primary keys in this table? Is it possible?


--
- Rikard

2009/11/12 Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com>:
> I have the following table:
>
> CREATE TABLE penalty_codes (
>        penalty_code varchar(10),
>        penalty_name varchar(32),
>        penalty_name_sv varchar(40),
>        penalty_id serial PRIMARY KEY
> );
>
> which I have been using for a year or two. Today I realized that the
> id-column being a primary key is really not useful, while the
> code-column is instead. Three other tables refer on the id-values so
> the column can certainly not be dropped, but is it possible to change
> the primary key to the code-column without breaking things?
>
> I tried this:
>
> ========================================
> SQL error:
> ERROR:  cannot drop constraint penalty_codes_pkey on table
> penalty_codes because other objects depend on it
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>
> In statement:
> ALTER TABLE "penalty_codes" DROP CONSTRAINT "penalty_codes_pkey"
> ========================================
>
> I'm aware of what CASCADE does when you drop a table for instance, but
> I have no idea what happens if you cascade drop a primary key.
>
> How can I switch the primary keys in this table? Is it possible?
>

You will have to remove foreign keys that point to this primary key
column before dropping it.  After doing so, you won't be able to
reapply the foreign keys unless you add a UNIQUE constraint to your
penalty_id column, preferrably also specifying NOT NULL.

Regards

Thom

Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> writes:
> I tried this:

> ========================================
> SQL error:
> ERROR:  cannot drop constraint penalty_codes_pkey on table
> penalty_codes because other objects depend on it
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.

> In statement:
> ALTER TABLE "penalty_codes" DROP CONSTRAINT "penalty_codes_pkey"
> ========================================

The system will normally tell you exactly what depends on the
constraint.  8.4 includes this in a DETAIL line, but prior releases
spit it out as separate NOTICE message(s).  I surmise that you are
running a pre-8.4 release and you have client_min_messages set to
suppress NOTICEs :-(

(Offhand I can't think of anything except foreign keys that would
depend on a PK constraint, but you may as well get the authoritative
statement from your DB.)

            regards, tom lane

I have an application where I need to "select" based on whether or not a
"text" column value contains a given substring. I have tried the "position"
function as follows, but it doesn't return anything:

select * in customers where position ('sub_string' in 'text_column') > 0;

Is there another way to do this?

Another question - how are upper and lower case handled when using "order
by"? In my experimenting, it seems to be doing a case insensitive compare,
but the docs I've read seem to indicate otherwise.

Thanks in advance,
Lynn

On Nov 12, 2009, at 7:29 PM, Lynn Manhart wrote:

> select * in customers where position ('sub_string' in 'text_column')
> > 0;

If you really have single quotes around the name of your text column,
position is looking for your string in that literal text. It should
not have single quotes. If your column name is a reserved word or has
special characters you can double quote it.

I also think you want select * "FROM" not "IN", so

SELECT * FROM customers WHERE position('sub_string' in
customers_text_column) > 0;

> Another question - how are upper and lower case handled when using
> "order by"? In my experimenting, it seems to be doing a case
> insensitive compare, but the docs I've read seem to indicate
> otherwise.

It depends on the locale settings when the cluster was created. On US
systems, typically uppercase precedes lowercase. E.g. "Zip" is before
"apple" in an ascending sort.



John DeSoi, Ph.D.

On 2009-11-13, Lynn Manhart <ManhartL@mstarmetro.net> wrote:
> I have an application where I need to "select" based on whether or not a
> "text" column value contains a given substring. I have tried the "position"
> function as follows, but it doesn't return anything:
>
> select * in customers where position ('sub_string' in 'text_column') > 0;
>
> Is there another way to do this?

ITYM:
select * FROM customers where position ('sub_string' in "text_column") > 0

perhaps using the like or ~ operators

 select * FROM customers where "text_column" LIKE '%sub_string%";

 select * FROM customers where "text_column" ~ 'sub_string";

these operatours apply some magic to the contents of substring so for
useful results care must be taken when preparing it.

> Another question - how are upper and lower case handled when using "order
> by"? In my experimenting, it seems to be doing a case insensitive compare,
> but the docs I've read seem to indicate otherwise.

depends on the locale setting, "C" will get you ordering by unicode
code point so 'A' < 'Z' < 'a' < 'z' < 'À' < 'Ý'

"EN-US" should get you "dictionary" ordering

show lc_collate;