如何更新作为主键且关联其他表外键的SKILL表sname字段？

Got it, let's break down how to fix this problem. The core issue here is that you're trying to modify SKILL.sname—which is both the primary key of SKILL and a foreign key in SPOSSESSED and SREQUIRED. To make things trickier, those foreign key columns are part of the child tables' primary keys, so they can't be null or left in an inconsistent state while you update the parent table.

Here are three solid approaches to handle this safely:

Approach 1: Use Cascading Updates (Recommended if Your Database Supports It) #

Most modern databases (PostgreSQL, MySQL, SQL Server, etc.) support ON UPDATE CASCADE for foreign keys. This lets you update the parent table's primary key, and the child tables' foreign keys will automatically sync up. Here's how to set it up:

1. Drop the existing foreign key constraints on the child tables first (replace the placeholder constraint names with your actual ones—check your database's schema to find them):

   ALTER TABLE SREQUIRED DROP CONSTRAINT SREQUIRED_sname_fkey;
   ALTER TABLE SPOSSESSED DROP CONSTRAINT SPOSSESSED_sname_fkey;
   

2. Recreate the foreign keys with cascading updates enabled:

   ALTER TABLE SREQUIRED ADD CONSTRAINT SREQUIRED_sname_fkey 
       FOREIGN KEY (sname) REFERENCES SKILL(sname) ON UPDATE CASCADE;
   
   ALTER TABLE SPOSSESSED ADD CONSTRAINT SPOSSESSED_sname_fkey 
       FOREIGN KEY (sname) REFERENCES SKILL(sname) ON UPDATE CASCADE;
   

3. Now update the SKILL table directly:

   UPDATE SKILL SET sname = 'your_new_skill_name' WHERE sname = 'old_skill_name';
   

   The child tables' sname values will automatically update to match—no extra work needed.

Approach 2: Manual Step-by-Step Update (For Databases That Don't Support Cascading Updates) #

If cascading updates aren't an option, you'll need to manually adjust the child tables first, since their primary keys depend on the old sname value.

1. Start a transaction to ensure all changes roll back if something goes wrong:

   BEGIN TRANSACTION;
   

2. Drop the primary key constraints on the child tables (since you can't modify a column that's part of a primary key without removing the constraint first):

   ALTER TABLE SREQUIRED DROP CONSTRAINT SREQUIRED_pkey;
   ALTER TABLE SPOSSESSED DROP CONSTRAINT SPOSSESSED_pkey;
   

3. Update the child tables' sname values to the new value:

   UPDATE SREQUIRED SET sname = 'your_new_skill_name' WHERE sname = 'old_skill_name';
   UPDATE SPOSSESSED SET sname = 'your_new_skill_name' WHERE sname = 'old_skill_name';
   

4. Update the parent SKILL table:

   UPDATE SKILL SET sname = 'your_new_skill_name' WHERE sname = 'old_skill_name';
   

5. Recreate the primary key constraints on the child tables (replace requires and any other columns with the actual columns in your child table primary keys):

   ALTER TABLE SREQUIRED ADD CONSTRAINT SREQUIRED_pkey 
       PRIMARY KEY (sname, requires);
   
   ALTER TABLE SPOSSESSED ADD CONSTRAINT SPOSSESSED_pkey 
       PRIMARY KEY (sname, [your_other_pk_column]);
   

6. Commit the transaction if everything looks good:

   COMMIT;
   

Critical Notes Before You Start #

• Backup your data first: Modifying primary keys and foreign keys is risky—always take a backup so you can restore if something breaks.
• Test in a staging environment: Don't run these commands directly on production! Test them in a copy of your database first to work out any kinks.
• Watch for locks: These operations will lock tables, so run them during low-traffic periods to avoid disrupting users.

内容的提问来源于stack exchange，提问作者Faye Lynnette