Conditional SQLite Check Constraint

Conditional SQLite check constraint?

How about:

CHECK (status = "Current" or (status = "Complete" and enddate is not null))

Conditional CHECK constraint

I added this CHECK constraint and it appears to work.

CHECK (crc32 IS NULL AND is_deleted = 't' OR crc32 IS NOT NULL AND is_deleted = 'f')

Check constraint with condition

A check constraints takes a boolean condition, so you'd have to frame this logic in a form of such a condition:

ALTER TABLE ADMITED_TABLE
ADD CONSTRAINT AAAA CHECK
(dateadmited IS NULL OR mark IS NULL);

Can CHECK constraints act like if else?

Absolutely, you can do this. See this sqlfiddle.

However, you need to make sure you bracket your logic properly. You should never mix ANDs and ORs in the same bracketing scope. So:

(col1 NOT NULL OR col2 NOT NULL AND col3 NULL)

Needs to become:

((col1 NOT NULL OR col2 NOT NULL) AND col3 NULL)

Or:

(col1 NOT NULL OR (col2 NOT NULL AND col3 NULL))

Depending on your intent.

How to On Conflict Replace with Condition (for SQLite)?

It is not possible to do this with a table constraint, you have to use triggers instead:

CREATE INDEX just_some_index ON MyTable(Name);

CREATE TRIGGER MyTable_Name_insert_newer
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) <= NEW.Date
BEGIN
    DELETE FROM MyTable
    WHERE Name = NEW.Name;
END;

CREATE TRIGGER MyTable_Name_insert_older
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) > NEW.Date
BEGIN
    SELECT RAISE(IGNORE);
END;

(In SQLite, a scalar subquery without a result returns just NULL, so inserting a new row makes both WHEN clauses fail.)

How to create a constraint for conditional unique values?

You can't create a CONSTRAINT for that, however, you can create a filtered unique index:

USE Sandbox;
GO

CREATE TABLE dbo.Student (ID int IDENTITY(1, 1) PRIMARY KEY,
                          FirstName varchar(100),
                          LastName varchar(100),
                          Active bit);

CREATE UNIQUE INDEX UQ_StudentName
    ON Student (FirstName,LastName)
    WHERE Active = 1;
GO

INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 1); --Success
GO
INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 0); --Success
GO
INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 0); --Success
GO
INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 1); --Fails;
GO

UPDATE dbo.Student
SET Active = 1
WHERE ID = 2; --Fails;
GO

SELECT *
FROM dbo.Student;
GO

DROP TABLE dbo.Student;

I however, highly recommend against the thought that names are unique. I (personally) shared my name and date of birth with another person at several places in my youth and businesses that treated names (and date of birth) as unique on their systems were such a head ache for the both of us (there really were places where I (or they) couldn't register without using an abbreviated name because we "already existed").

---

Related Topics