usersoil table/ sqlite3.IntegrityError: UNIQUE constraint failed

[Camilla Negri]

Hi there,

I'm getting a ton of errors when I prepare and then upload the table for QGIS - and the most recent is: 

Could not write to table my_usersoil4 in database 

, line 2706, in importCsv

cursor.execute(sql, tuple(line))

sqlite3.IntegrityError: UNIQUE constraint failed: my_usersoil4.SNAM

I thought this could be because I might have more than one row for a given SNAM but I check and re-checked:

  dplyr::group_by(SNAM) %>%
  dplyr::mutate(
    completeness_score = rowSums(!is.na(across(where(is.numeric))), na.rm = TRUE)
  ) %>%
  dplyr::slice_max(completeness_score, n = 1, with_ties = FALSE) %>%
  dplyr::select(-completeness_score) %>%
  dplyr::ungroup()%>%
  dplyr::mutate(SNAM = trimws(toupper(SNAM)))  # trim any hidden spaces

as well as:

  dplyr::mutate(SNAM_trim = trimws(SNAM)) %>%
  dplyr::group_by(SNAM_trim) %>%
  dplyr::filter(n() > 1)

nrow(dupes)
unique(dupes$SNAM_trim)

and I get zero duplicates this way. Also I have made my user table using this dataset: 

Cordeiro, Marcos R C; Lelyk, Glenn; Kröbel, Roland; Legesse, Getahun; Faramarzi, Monireh; Masud, Mohammad Badrul; McAllister, Tim (2017): Deriving Canada-wide soils dataset for use in Soil and Water Assessment Tool (SWAT) [dataset]. PANGAEA, https://doi.org/10.1594/PANGAEA.877298, Supplement to: Cordeiro, MRC et al. (2018): Deriving a dataset for agriculturally relevant soils from the Soil Landscapes of Canada (SLC) database for use in Soil and Water Assessment Tool (SWAT) simulations. Earth System Science Data, 10(3), 1673-1686, https://doi.org/10.5194/essd-10-1673-2018

which contains all Canadian soils and therefore more than I need for my study area.

I would appreciate feedback on this.

Many thanks and best wishes,

Camilla Negri

[Chris George]

You could look at the table constraints, though the message suggests
that there is a uniqueness constraint on SNAM.

I use SqliteStudio, which is free and, I find, very useful. You can
probably find other tools that do the same, but in SqliteStudio you can
open the table, click on constraints to see what they are. I would then
remove any constraints, import the csv, and sort on the SNAM column
(assuming that is the constraint) by just double clicking on the header.
You can easily see if there are any duplicates.

if the file is large you could also open an SQL editor and use the
command (again to check SNAM)

SELECT SNAM, COUNT(SNAM) FROM usersoil GROUP BY SNAM ORDER BY
COUNT(SNAM) DESC

and any counts greater than 1 will be displayed first.

Once resolved you can reinstate the constraint if you wish: QSWAT does
not need it.

Chris

On 10/16/2025 11:10 PM, 'Camilla Negri' via SWAT-user wrote:
> Hi there, I'm getting a ton of errors when I prepare and then upload the
> table for QGIS - and the most recent is:   Could not write to table
> my_usersoil4 in database  , line 2706, in importCsv cursor. execute(sql,
> tuple(line)) sqlite3. IntegrityError: 

> ZjQcmQRYFpfptBannerStart
> This Message Is From an External Sender
> This message came from outside your organization.
> ZjQcmQRYFpfptBannerEnd

> Hi there,
>
> I'm getting a ton of errors when I prepare and then upload the table for
> QGIS - and the most recent is:
>
> Could not write to table my_usersoil4 in database
>

> , line 2706, *in importCsv*
>
> *cursor.execute(sql, tuple(line))*
>
> *sqlite3.IntegrityError: UNIQUE constraint failed: my_usersoil4.SNAM*

> Earth System Science Data, 10(3), 1673-1686, *https://doi.org/10.5194/
> essd-10-1673-2018*

>
>
> which contains all Canadian soils and therefore more than I need for my
> study area.
>
>
> I would appreciate feedback on this.
>
>
> Many thanks and best wishes,
>
> Camilla Negri
>

> --
> You received this message because you are subscribed to the Google
> Groups "SWAT-user" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to swatuser+u...@googlegroups.com
> <mailto:swatuser+u...@googlegroups.com>.
> To view this discussion visit https://groups.google.com/d/msgid/
> swatuser/124c13c8-286a-4650-8fa9-48d2a9215900n%40googlegroups.com
> <https://urldefense.com/v3/__https://groups.google.com/d/msgid/
> swatuser/124c13c8-286a-4650-8fa9-48d2a9215900n*40googlegroups.com?
> utm_medium=email&utm_source=footer__;JQ!!KwNVnqRv!CbiU9FNaZo-
> CYdsqaSdK0vcfbViFq7SPKFD05H96bNtxsEyi8xov7LnUr3A6AncX5z1wvZyjclksIz0ypaf_Ww$>.