Skip to content

Changing multiple columns of composite primary key is not supported #933

@kvch

Description

@kvch

I added a new test TestAlterPrimaryKeyColumns with multiple test cases in #930.
However, we do not yet support altering multiple columns in the same composite key.

A test that fails:

         {
             name: "alter a composite primary key: two column changes type",
             migrations: []migrations.Migration{

            {
                Name: "01_create_table",
                Operations: migrations.Operations{
                    &migrations.OpCreateTable{
                        Name: "people",
                        Columns: []migrations.Column{
                            {
                                Name: "id1",
                                Type: "int",
                                Pk:   true,
                            },
                            {
                                Name: "id2",
                                Type: "int",
                                Pk:   true,
                            },
                            {
                                Name:     "name",
                                Type:     "varchar(255)",
                                Nullable: true,
                            },
                        },
                    },
                },
            },
            {
                Name: "02_alter_column",
                Operations: migrations.Operations{
                    &migrations.OpAlterColumn{
                        Table:  "people",
                        Column: "id1",
                        Type:   ptr("bigint"),
                        Up:     "CAST(id1 AS bigint)",
                        Down:   "SELECT CASE WHEN id1 < 2147483647 THEN CAST(id1 AS int) ELSE 0 END",
                    },
                    &migrations.OpAlterColumn{
                        Table:  "people",
                        Column: "id2",
                        Type:   ptr("bigint"),
                        Up:     "CAST(id2 AS bigint)",
                        Down:   "SELECT CASE WHEN id2 < 2147483647 THEN CAST(id2 AS int) ELSE 0 END",
                    },
                },
            },
        },
        afterStart: func(t *testing.T, db *sql.DB, schema string) {
            bigint := "31474836471" // A value larger than int can hold
            integer := "100"

            // Inserting a row with integer id into the old schema should succeed
            MustInsert(t, db, schema, "01_create_table", "people", map[string]string{
                "id1":  integer,
                "id2":  integer,
                "name": "alice",
            })
            // Inserting a row with integer bigint id into the old schema should fail
            MustNotInsert(t, db, schema, "01_create_table", "people", map[string]string{
                "id1":  bigint,
                "id2":  integer,
                "name": "alice",
            }, testutils.NumericValueOutOfRangeErrorCode)

            // Inserting a row with bigint id into the new schema should succeed
            MustInsert(t, db, schema, "02_alter_column", "people", map[string]string{
                "id1":  bigint,
                "id2":  bigint,
                "name": "alice",
            })
        },
        afterRollback: func(t *testing.T, db *sql.DB, schema string) {
        },
        afterComplete: func(t *testing.T, db *sql.DB, schema string) {
            // Inserting a row with integer bigint into the new schema should succeed
            MustInsert(t, db, schema, "02_alter_column", "people", map[string]string{
                "id1":  "31474836472",
                "id2":  "31474836472",
                "name": "bob",
            })
        },
    },

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions