0

I'd like to have a constraint on my table Visitor that permits the insertion of duplicate data. However, this should only be permitted if a boolean (IsUnique) is set to true.

E.g. (expected results for each insert after the -->): 

INSERT INTO Visitor (FirstName, LastName, IsUnique) VALUES ('John', 'Doe', 'True') --> ok
INSERT INTO Visitor (FirstName, LastName, IsUnique) VALUES ('John', 'Doe', 'True') --> not ok. already exists
INSERT INTO Visitor (FirstName, LastName, IsUnique) VALUES ('John', 'Doe', 'False') --> ok. IsUnique is set to false
INSERT INTO Visitor (FirstName, LastName, IsUnique) VALUES ('John', 'Doe', 'False') --> also ok because IsUnique is set to false

This is what I came up with so far:

ALTER TABLE Visitor ADD CONSTRAINT CT_UniqueVisitor UNIQUE NONCLUSTERED (Title, Firstname, Lastname, Company, IsUnique)

This doesn't allow insertion of duplicate rows at all though. Please help.

markai
  • 101
  • 1
  • 8
  • Is this for oracle ? – Sagar Jani Mar 27 '17 at 09:37
  • 1
    Tag your database – Oto Shavadze Mar 27 '17 at 09:38
  • Are your four inserts a single example or two examples? Are we allowed both the rows `John, Doe, False` and `John, Doe, True`? – Damien_The_Unbeliever Mar 27 '17 at 09:41
  • It's for mssql. Sorry for not mentioning that. These four inserts are a single example. If True then there should not be any other row with the same values. If false duplicates are allowed – markai Mar 27 '17 at 10:19
  • The question makes no sense. If the records are true duplicates, `N-1` of them are redundant, and add no information to the existing record, so why would you allow them to exist? If they are *not* duplicates, your primary key is defined wrong (since not all non-key columns depend on it) – joop Mar 27 '17 at 10:21

2 Answers2

0

Data Integrity - RDBMS Core Principal

Here, you are trying to achieve dynamic constraints, I doubt any RDBMS would support this because any RDBMS database works on Data Integrity as its core principle and the data constraints are defined as per specific needs of an application.

Solution

To solve your problem, I would rather create a trigger on INSERT which is triggered before data is inserted and it checks if this field is true or false.

If isUnique is true then it needs to be checked programatically if the duplicate exists or else allow insertion anyway.

Community
  • 1
  • 1
Sagar Jani
  • 257
  • 1
  • 10
  • 1
    In contrast with your doubt, many database systems have some means by which you can implement subset checks (e.g. SQL Server's `filtered index`s, Postgre's `partial index`s, I believe something can be done with function-based indexes in Oracle, etc). These may be appropriate but to know whether they are or which concrete approach to take, we need more clarity from the OP in their question, including identifying which database system they're using (since each approach is vendor specific) – Damien_The_Unbeliever Mar 27 '17 at 09:53
0

You can move integrity checks out of RDBMS to the code. Consider two queries:

INSERT INTO Visitor (FirstName, LastName, IsUnique) 
SELECT * FROM ('John', 'Doe', 'True') AS tmp
WHERE NOT EXISTS (
    SELECT FirstName, LastName FROM Visitor 
    WHERE FirstName= 'John'
     AND LastName = 'Doe'
) LIMIT 1;


INSERT INTO Visitor (FirstName, LastName, IsUnique) VALUES ('John',
'Doe', 'False')

You will select which one to use on the application code side depending on IsUnique value. Although, the first one would not affect performance.

S. Stas
  • 800
  • 4
  • 8