Subscribe to this thread
Home - General / All posts - Adding a unique value constraint
danb


1,822 post(s)
#06-Sep-21 22:50

I want to add a constraint on an attribute to ensure that newly entered values are always unique. I had a quick trawl around the internet and then the constraints topic, and on my simple test table, it looks like the following should work:

Constraint Expression:

[VALUE] NOT IN (SELECT [VALUE] FROM [Table])

This however yields a ‘Cannot add constraint’ error.

The following constraint (though it does the opposite of what I want) does parse ok.

Constraint Expression:

[VALUE] IN (SELECT [VALUE] FROM [Table])

Can anyone shed a bit of light on this or give me a pointer how to get this working?

Thanks in advance


Landsystems Ltd ... Know your land | www.landsystems.co.nz

oeaulong

345 post(s)
#06-Sep-21 23:36

Can this not be achieved with assigning an Index to [Value] field making it by default unique?

danb


1,822 post(s)
#06-Sep-21 23:38

Yes, but I'm keen to do it with a constraint because that seems like the correct place to do it rather than a workaround


Landsystems Ltd ... Know your land | www.landsystems.co.nz

oeaulong

345 post(s)
#07-Sep-21 00:17

My SQL chops are weak, so I cannot really explain your original question. I replicated the message. I notice that in other SQL flavors that Unique is used in an expression for a constraint. I'll keep my ear to this thread though, as I might learn a trick or to.

danb


1,822 post(s)
#07-Sep-21 00:26

Thanks for having a look. I saw the UNIQUE keyword for SQLServer and tried this syntax also (just on the off chance). Perhaps using an index is a good way to go given performance and the fact that M9 tables can have a vast number of rows? It would be nice to learn a bit more about constraints however as they seem to have a lot of potential for maintaining data integrity in data entry scenarios.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb

9,824 post(s)
#07-Sep-21 01:24

Dan, this may be an impertinent question, but why? What is the purpose?

You must want something else besides uniqueness--because you already have that, in mfd_id.

You could duplicate, or offset, or multiply the mfd_id value, which would guarantee another unique value, but that would beg the same question.

There must be more to the objective.

My guess is that you want to ensure a unique value of a different type, e.g. a string.

danb


1,822 post(s)
#07-Sep-21 06:31

Hi Tim, I want to ensure that any new values added to the [VALUE] attribute are unique.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb

9,824 post(s)
#07-Sep-21 06:33

But why? (You still haven't said. Puzzling!)

And what is the data type?

And as I said, if uniqueness were all you cared about, you could just copy mfd_id.

It clearly isn't all you care about. What else?

lionel

769 post(s)
#08-Sep-21 21:56

We should have a way in SQL to name cell content like @[VALUE] for value inside cell of column [VALUE]

SQL------------------------

INSERT INTO [VALUE]

SELECT @[VALUE]

WHERE NOT EXISTS 

(

SELECT [mfd_id]  

WHERE Word = @[VALUE]

)

SQL---------------------------------

INSERT INTO [VALUE]

   SELECT @[VALUE]

WHERE NOT EXISTS (SELECT *  WHERE [VALUE] = @[VALUE]) 

********************************************************************* in GUI way using table for add data in cell ; manifold handle insert ( using SQL) implicitly so need focus on SQL context and SQ for constraint .Expression. It ll be nice that the validation button exist insteadhave this information after and see that the SQL we write are not save !!

Attachments:
constraintUnique_During_GUIinsert.png
SQL_INSERT_onlyone.map


union, doc , APIand most important deepl & keyboard shortcut

lionel

769 post(s)
#08-Sep-21 22:58

https://manifold.net/doc/mfd9/sql_operators.htm

I was thinking of " IN " ( apply to values) with a " NOT " before " IN " to have """NOT IN"" instead " EXIST " ( apply work if one record exist in a table ) like you .....

SQL

ALTER TABLE Table

ADD CONSTRAINT uniquecolvalue AS

[[

[VALUE] NOT IN ( SELECT [VALUE] FROM [Table]);

]]

but don't work because the actual VALUE ( dynamic value that will be insert in value column ) that manifold handle should be accessible using specific syntax and so his name can't be be [VALUE] but @[VALUE] or something else ... Do we need to create a Virtual table for the select ? ( use a join )

<value> IN (<query>)

Returns True (1) if the value occurs in the results of the query.

'Durango' IN (SELECT [NAME] FROM [Mexico Table])

Returns 1.

'Vermont' IN (SELECT [NAME] FROM [Mexico Table])

Returns 0.

<value> IN (<value>, ...)

Returns True (1) if the first value occurs in the list of values.

'Tom' IN ('Tom', 'Dick', 'Harry')

Returns 1.

'John' IN ('Tom', 'Dick', 'Harry')

Returns 0.

EXISTS <table>

Returns True (1) if the argument contains at least one record.

Attachments:
SQL_INSERT_onlyone.map


union, doc , APIand most important deepl & keyboard shortcut

lionel

769 post(s)
#08-Sep-21 23:24

SQL expression ( ? ...) has no " ; " in the end

NOT IN work using SQL Command """ ? 1 NOT IN (SELECT [VALUE] FROM [Table]) """

strange manifold documentation use in SQL code " [] " and sometine nothing ( open end glyph/mark [] ) around variable name !!

Example: Expression Context and Computed Fields (manifold.net)

Attachments:
SQL_INSERT_onlyone.map


union, doc , APIand most important deepl & keyboard shortcut

Dimitri


6,713 post(s)
#09-Sep-21 03:30

strange manifold documentation use in SQL code " [] " and sometine nothing ( open end glyph/mark [] ) around variable name !!

See the Identifiers, Constants, and Literals topic.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.