Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add support for table constraints to create_table #580

Open
5 of 9 tasks
kvch opened this issue Jan 9, 2025 · 0 comments
Open
5 of 9 tasks

Add support for table constraints to create_table #580

kvch opened this issue Jan 9, 2025 · 0 comments
Assignees
@kvch
Labels
enhancement New feature or request sql2pgroll Issues relating to the sql2pgroll package

Comments

@kvch
Copy link
Contributor

kvch commented Jan 9, 2025 •
edited
Loading

At the moment it is not possible to add table constraints to tables during creation time.
We must extend create_table to support creating table constraint.
@kvch kvch self-assigned this Jan 9, 2025
@kvch kvch added enhancement New feature or request sql2pgroll Issues relating to the sql2pgroll package labels Jan 9, 2025
This was referenced Jan 9, 2025
Closed
Merged
kvch added a commit that referenced this issue Jan 13, 2025
@kvch
Add constraints option to create_table and unique constraint su…
43381e9 
…pport (#585)

This PR adds support for a new option of `create_table` operation named
`constraints`.
It expects a list of `constraints` that is defined on the table when the
table is created.

At the moment the only constraint we support is `unique`. But it
includes support for all options
of unique constraints including `NULLS NOT DISTINCT`, index
configuration settings, constraint deference, etc. Once I am done with
these table constraints, I will open a follow-up PR to extend the
constraint options for column constraints and `create_constraint`
operation.

Example migration:
```json
{
  "name": "50_create_table_with_table_constraint",
  "operations": [
    {
      "create_table": {
        "name": "phonebook",
        "columns": [
          {
            "name": "id",
            "type": "serial",
            "pk": true
          },
          {
            "name": "name",
            "type": "varchar(255)"
          },
          {
            "name": "city",
            "type": "varchar(255)"
          },
          {
            "name": "phone",
            "type": "varchar(255)"
          }
        ],
        "constraints": [
          {
            "name": "unique_numbers",
            "type": "unique",
            "columns": ["phone"],
            "index_parameters": {
              "include_columns": ["name"]
            }
          }
        ]
      }
    }
  ]
}
```

The table definition above turns into this table in PostgreSQL:

```
postgres=# \d phonebook
                                    Table "public.phonebook"
 Column |          Type          | Collation | Nullable |                Default
--------+------------------------+-----------+----------+---------------------------------------
 id     | integer                |           | not null | nextval('phonebook_id_seq'::regclass)
 name   | character varying(255) |           | not null |
 city   | character varying(255) |           | not null |
 phone  | character varying(255) |           | not null |
Indexes:
    "phonebook_pkey" PRIMARY KEY, btree (id)
    "unique_numbers" UNIQUE CONSTRAINT, btree (phone) INCLUDE (name)
```

Part of #580
@kvch kvch mentioned this issue Jan 13, 2025
Merged
kvch added a commit that referenced this issue Jan 16, 2025
@kvch
Add support for CHECK constraints in create_table operation (#588)
e919085 
This PR adds a new type of constraints to `create_table` operation,
named `check`. So from now, it is possible to configure table level
check constraints.

Available settings:
* `check`: required, check expression
* `no_inherit`: disable constraint inheritance in child tables (default:
`false`)

Example:
```json
{
  "name": "50_create_table_with_table_constraint",
  "operations": [
    {
      "create_table": {
        "name": "phonebook",
        "columns": [
          {
            "name": "id",
            "type": "serial",
            "pk": true
          },
          {
            "name": "name",
            "type": "varchar(255)"
          }
        ],
        "constraints": [
          {
            "name": "name_must_be_present",
            "type": "check",
            "check": "length(name) > 0"
          }
        ]
      }
    }
  ]
}
```

Part of #580
@jokersoft jokersoft mentioned this issue Jan 23, 2025
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@kvch kvch

Labels
enhancement New feature or request sql2pgroll Issues relating to the sql2pgroll package
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@kvch