Обсуждение: Constraint allowing value up to 2 times but no more than 2 times
Greeetings.
I'm trying to come up with a way to allow one column to have the same value up to two times, but no more than two times. I attempted adding a constraint to check the count of the value in the field - count (trsqqq) <=2 but aggregate functions are not allowed in constraints. Is there another way to do this? Any help would be greatly appreciated.--
Dara J. Olson Unglaube
Dara J. Olson Unglaube
Aquatic Invasive Species Coordinator, Spatial Database Manager
Great Lakes Indian Fish & Wildlife Commission
P.O. Box 9, 72682 Maple Street
Odanah, WI 54861
Great Lakes Indian Fish & Wildlife Commission
P.O. Box 9, 72682 Maple Street
Odanah, WI 54861
(715) 682-6619 ext.2129
On 10/12/15 12:04 PM, Dara Unglaube wrote: > Greeetings. > I'm trying to come up with a way to allow one column to have the same > value up to two times, but no more than two times. I attempted adding a > constraint to check the count of the value in the field - count > (trsqqq) <=2 but aggregate functions are not allowed in constraints. Is > there another way to do this? Any help would be greatly appreciated. The safest bet is some kind of unique constraint. That would require having a separate count field, which you would limit to being 1 or 2 via a CHECK constraint. You could use a trigger to set the value based on what's already in the table. By the way, the issue with doing a simple count is that it's not safe from race conditions, like an insert and a delete happening together. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On 10/12/15 1:41 PM, Dara Unglaube wrote: > I created a view with the column of interest and a column of the count. > How do I do a check constraint on a view or do it all at once as a > subquery? Could you provide an example of how to create? Please don't top-post. And do include the mailing list so others can learn. The constraint would go on the table, not the view. The column would need to not be a count, but a 'record number' or something similar. So you'd have one record with 'record_number=1' and the second with 'record_number=2'. CREATE TABLE ...( ... , record_number smallint NOT NULL CONSTRAINT record_number_must_be_1_or_2 CHECK( record_number BETWEEN 1 AND 2 ) ); -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com