Insert only if two columns don't have the same value for each one of them on the same row-CodePudding

If I have a table like

id            col1         col2             col3
1             John         Edward           any OK
2             John         David            any OK
3             John         Sam              any OK
4             David        Sam              any OK
5             John         Sam              any * Not allowed it already exists.

So, John can be repeated on col1, but it is not allowed to repeat John and Sam on col1 and col2.

col1 and col2 are not unique.

How to do insert and update on that table in SQLite?

CodePudding user response：

Just create a UNIQUE constraint that spans col1 and col2:

CREATE TABLE table_name(
    id INTEGER PRIMARY KEY,
    col1 TEXT,
    col2 TEXT,
    col3 TEXT,
    UNIQUE(col1, col2)
);

In your application, just handle the constraint violation exception.

CodePudding user response：

To insert and update records in a table with the constraints you have described, you can use a combination of INSERT and UPDATE statements with a WHERE clause to check for the presence of duplicate values in the col1 and col2 columns. Here is an example of how you can do this in SQL:

-- Insert a new record into the table
INSERT INTO my_table (col1, col2, col3)
VALUES ('John', 'Sam', 'any OK')

-- Check if the record already exists
IF EXISTS (SELECT * FROM my_table WHERE col1 = 'John' AND col2 = 'Sam')
BEGIN
    -- If the record already exists, update the col3 value to indicate that it is not allowed
    UPDATE my_table
    SET col3 = 'any * Not allowed it already exists.'
    WHERE col1 = 'John' AND col2 = 'Sam'
END

In this example, an INSERT statement is used to add a new record to the table. Then, an IF statement is used to check if the record already exists in the table. If the record exists, an UPDATE statement is used to change the value of the col3 column to indicate that the combination of values in col1 and col2 is not allowed.

You can use a similar approach to handle updates to existing records. For example, you can use a SELECT statement to retrieve the current values of col1 and col2 for a given record, and then use an UPDATE statement to change the values if necessary. Here is an example of how you can do this:

-- Retrieve the current values of col1 and col2 for a given record
SELECT col1, col2 FROM my_table WHERE id = 5

-- Update the col1 and col2 values for the record
UPDATE my_table
SET col1 = 'David', col2 = 'Edward'
WHERE id = 5

-- Check if the updated values already exist in the table
IF EXISTS (SELECT * FROM my_table WHERE col1 = 'David' AND col2 = 'Edward')
BEGIN
    -- If the updated values already exist, update the col3 value to indicate that it is not allowed
    UPDATE my_table
    SET col3 = 'any * Not allowed it already exists.'
    WHERE id = 5
END

In this example, a SELECT statement is used to retrieve the current values of col1 and col2 for the record with an id of 5. Then, an UPDATE statement is used to change the values of col1 and col2 for the record. Finally, another IF statement is used to check if the updated values already exist in the table, and if they do, the col3 value is updated to indicate that the combination of values is not allowed.

Keep in mind that this is just an example of how you can handle inserts and updates with the constraints you have described. You may need to adjust the SQL statements to fit the specific requirements of your table and your application.