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.

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

Check constraint on table prevents new record being added #530

Closed
mh00h opened this Issue Feb 24, 2016 · 5 comments

Comments

Projects
None yet
5 participants
@mh00h
Copy link

mh00h commented Feb 24, 2016

I'm having difficulty adding a record when a check constraint is defined. I can provide a db example offline if anyone can help me?

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Feb 24, 2016

In theory, we'd like to. Most of our dev's seem to be taking a break recently now though, so we're kind of short on people with knowledge & free time. 😦

@Jeff-G

This comment has been minimized.

Copy link

Jeff-G commented Apr 4, 2016

I just came across a concrete example for this:

CREATE TABLE "AAA" (
    "FIELD" TEXT,
    CONSTRAINT "AAA-FIELD-EVEN_LENGTH_CHECK"
        CHECK(LENGTH("FIELD") % 2 = 0)
)

The fix for issue #505 seemed to fix some of these (check constraints with logical connectives such as AND/OR before that fix also exhibited this behavior), but the constraint parser clearly has some unresolved issues. The above table prevents inserting new records (or browsing the data within it) in the April 3, 2016 nightly build.

@mgrojo mgrojo referenced this issue Mar 18, 2018

Closed

CHECK constraint fails on NOT NULL boolean field #1350

5 of 14 tasks complete

mgrojo added a commit that referenced this issue Jul 15, 2018

Issue #530: constraints on table prevents new record being added
This adds a new dialog for adding records to a table. The current approach
is broken for several cases where foreign keys and check constraints are
impeding the insertion of an empty record. With the dialog, the user can
inspect the constraints (tooltip) and type of fields and add values
consistent with the requirements. The data is only inserted when the user
presses the Save button.

The dialog is modelled after the Edit Table or Edit Index dialog. An upper
frame allows entering the data using widgets. The lower frame previews the
SQL statement that will be used.

The old approach for adding records is still accessible pressing Tab on
the last cell of the table.

MKleusberg added a commit that referenced this issue Aug 27, 2018

Issue #530: constraints on table prevents new record being added (#1477)
* Issue #530: constraints on table prevents new record being added

This adds a new dialog for adding records to a table. The current approach
is broken for several cases where foreign keys and check constraints are
impeding the insertion of an empty record. With the dialog, the user can
inspect the constraints (tooltip) and type of fields and add values
consistent with the requirements. The data is only inserted when the user
presses the Save button.

The dialog is modelled after the Edit Table or Edit Index dialog. An upper
frame allows entering the data using widgets. The lower frame previews the
SQL statement that will be used.

The old approach for adding records is still accessible pressing Tab on
the last cell of the table.

* Fix build problem introduced in previous commit on this branch

* Dialog as fallback for failure after empty row insertion and read only text

The insertion of an empty row is always tried. When it fails due to
constraints and foreign keys, the Add Record Dialog is open so the user
can enter values for the new record considering the constraints.

When the table has not constraints, or the row insertion provides valid
values, the user is still able to insert rows using the simple approach.

SQL preview in dialog is now read-only.

* Visual improvements for the Add Record dialog

QLineEdit as item delegate for the value, so it is more visible that we
are supposed to edit the value.

Remove last end-of-line in tool-tip.

* Improvements in the "Add Record" dialog

Display of NULL values using DisplayRole (no focus) or place holder text
(when focus).

Set value to NULL through a context menu and shortcut in the value line
edit.

Take text type affinity into account for quoting or not entered numbers.
New isType and affinity functions in sqlitetypes.

Clarify wording of constraints in tooltip for value. Added the same tooltip
for the type.

Escape quotes inside string values.

Removed unused parameters warnings.

Other wording or code improvements based on the pull-request review: #1477.

* User access to the Add Record dialog

The Add Record dialog is now accessible for the user. The New Record button
is converted to a QToolButton and a new pop-up menu is added to it for
invoking the in-line table insertion (New Record) or the Add Record dialog
(Insert Values...). What's This information for the button updated.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Aug 27, 2018

Sorry for taking so long with this but we have finally merged a change by @mgrojo which should fix this problem 😄 If you're still around, can you give tomorrow's nightly build a try and see if it's working now, @mh00h and @Jeff-G?

@mh00h

This comment has been minimized.

Copy link
Author

mh00h commented Aug 31, 2018

Thanks guys. I'm not working on the previous project right now. Nevertheless thank you to the individual who merged this.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Aug 31, 2018

No worries @mh00h, thanks for taking the time to report it. It did help, even though it took us a while to get there. 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment