Postgresql Constraint - Only One Row Can Have Flag Set

PostgreSQL constraint - only one row can have flag set

You can create a unique partial index on that column only for true values:

create unique index on my_table (actual) 
where actual = true;

SQLFiddle: http://sqlfiddle.com/#!15/91f62/1

How to restrict only 1 row to get updated in postgres?

You can use the pseudo column CTID here -

Update Table_name
   SET flag='U'
 WHERE CTID IN (SELECT CTID FROM Table_name WHERE id = 1 LIMIT 1);

How to have a column at `true` for only one in several linked rows, and the rest at `false`?

This is tricky. Here is why:

You want each user to have exactly one main phone number. So, if a user has only one phone number, this is the main number. If a user has four numbers, then one must be the main number, the others secondary numbers.

... At least at the time of COMMIT to the table!

Let's say a user has two entries. 123456 is the main number 654321 a secondary one. Now the user wants 654321 to become their main number.

This must work:

start transaction;
update user_phone set main = true where number = '654321';
-- Just for this microsecond there are two main numbers for this user.
update user_phone set main = false where number = '123456';
commit;
-- The user has one main number again.

And this:

start transaction;
update user_phone set main = false where number = '123456';
-- Just for this microsecond there are only secondary numbers for this user.
update user_phone set main = true where number = '654321';
commit;
-- The user has one main number again.

But not this:

start transaction;
update user_phone set main = true where number = '654321';
commit;
-- There are two main numbers now for the user.

or this:

start transaction;
update user_phone set main = false where number = '123456';
commit;
-- There are only secondary numbers now for the user.

In some DBMS you could solve this with deferred constraints, i.e. constraints that only apply on COMMIT. In the users table you'd have a main phone ID additionally to your phone table then and you'd insert a user, then their phones, then update the user with their main phone in one transaction. On COMMIT all data would be consistent. If not, the violated foreign key constraint would fire. MySQL doesn't feature deferred constraints.

Here is how I would solve this: Give the phone numbers a rank. This can be 1, 2, 3, ... or 10, 20, 30, ... It doesn't actually matter; you'd consider the lowest rank number the main phone.

create table user_phone
(
  user_id int         not null,
  phone   varchar(20) not null,
  prio    int         not null,
  unique (user_id, phone),
  unique (user_id, prio)
);

The related query:

select 
  user_id, phone,
  case when row_number() over (partition by user_id order by prio) = 1
       then 'main'
       else 'secondary'
  end as type
from user_phone
order by user_id, type;

If you want to make another phone the main phone, then just change the ranks. E.g.

update user_phone set prio = prio + 1 where user_id = 1;
-- Still the same order, still the same main number.
update user_phone set prio = 1 where user_id = 1 and phone = '54321';
-- Phone '54321' is the new main number for user 1.

The analytic function ROW_NUMBER requires MySQL version 8. It isn't available in earlier versions.

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c8a483f769db850f90cc4a6059e59832

Constraint for only one record marked as default

Here's a modification of Damien_The_Unbeliever's solution that allows one default per FormID.

CREATE VIEW form_defaults
AS
SELECT FormID
FROM whatever
WHERE isDefault = 1
GO
CREATE UNIQUE CLUSTERED INDEX ix_form_defaults on form_defaults (FormID)
GO

But the serious relational folks will tell you this information should just be in another table.

CREATE TABLE form
FormID int NOT NULL PRIMARY KEY
DefaultWhateverID int FOREIGN KEY REFERENCES Whatever(ID)

Can a Postgres constraint limit a table to one true value, but many false values?

That can be done with a partial unique index:

create unique index on logon_state (region, chanel)
  where live;

This assumes that the domains (or types) region and chanel can be compared properly. If those are record types with multiple fields, this will probably not work.

CONSTRAINT to check values from a remotely related table (via join etc.)

CHECK constraints cannot currently reference other tables. The manual:

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

One way is to use a trigger like demonstrated by @Wolph.

A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the first choice to enforce referential integrity. Related answer on dba.SE with detailed instructions:

• Enforcing constraints “two tables away”

Another option would be to "fake" an IMMUTABLE function doing the check and use that in a CHECK constraint. Postgres will allow this, but be aware of possible caveats. Best make that a NOT VALID constraint. See:

• Disable all constraints and table checks while restoring a dump

Mysql unique constraint allowing single row for a combination

A normal way to do this is to extract a separate table to hold the default price :

CREATE TABLE price (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name, price)
) ;

CREATE TABLE defaultPrice (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name),
    FOREIGN KEY(name, price) REFERENCES price(name, price)
);

Most people will advise introducing surrogate keys:

CREATE TABLE item (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    UNIQUE(name)
);

CREATE TABLE price (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId, price),
    FOREIGN KEY (itemId) REFERENCES item (id)
) ;

CREATE TABLE defaultPrice (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId),
    FOREIGN KEY (itemId, price) REFERENCES price (itemId, price)
);

Could I make a column in a table only allows one 'true' value and all other rows should be 'false'

Rather than having the boolean attribute in the table, you could have another table that contains one row and points to the row in the original table that you consider true.

Changing the true value is a matter of updating the foreign key in the TrueRow table.

---

Related Topics