Help with unique constraint

Question

Help with unique constraint

daytonbrown2

Say Hey Kid

Points: 666
More actions
October 2, 2024 at 1:51 pm

#4464514
Hi All,
I have a table using the below structure. Currently it's set up to be unique over names and email addresses. I have a new requirement to allow emails with no first or last name(not null though, empty string). However, if the email address already lives in the table, then don't add it. Now, even though the code I write is perfect(ha!), I would like to figure out a unique constraint on this, but I'm stuck. If anyone has a good idea, I'd love to hear it. I'm also open to...you can't...and I need to just add in a process to find any that sneak in. Thanks!
```
CREATE TABLE #unique_names
(
    id INT IDENTITY(1, 1)
   ,first_name VARCHAR(200) NOT NULL
   ,last_name VARCHAR(200) NOT NULL
   ,email_address VARCHAR(254) NOT NULL
);

CREATE UNIQUE INDEX ix_unique
ON #unique_names (first_name, last_name, email_address);

INSERT INTO #unique_names (first_name, last_name, email_address)
VALUES
('Joe', 'Schmo', 'jschmo@bad_domain.com')
,('Jane', 'Schmo', 'jschmo@bad_domain.com')
,('', '', 'email_address_no_name@bad_domain.com');

/*How do I prevent this*/
INSERT INTO #unique_names (first_name, last_name, email_address)
VALUES
('', '', 'jschmo@bad_domain.com');
```

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1357 More actions · Answer 1

Just exclude first_name & last_name from the constraint -- per your new requirement, they are no longer part of the definition of unique. e.g.,

CREATE UNIQUE INDEX ix_unique_email
ON #unique_names (email_address);

If you need to prevent duplicates of non-blank names, then you may also need a unique index with a where clause -- e.g., something like

CREATE UNIQUE INDEX ix_unique_name
ON #unique_names (first_name, last_name)
WHERE first_name <> '' AND last_name <> '';

'';

daytonbrown2 Say Hey Kid Points: 666 More actions · Answer 2

I was maybe not clear enough, email address alone is not unique in the table First, last and email address is unique. An email address can appear multiple times with different names. But an email cannot appear with an empty name AND populated name fields. Thus trying to figure out the best way to prevent the last insert statement from running.

Ant-Green SSC Guru Points: 113498 More actions · Answer 3

You'll need some app / query logic to prevent it.

IF EXISTS(SELECT 1 FROM #unique_names WHERE email = @email AND first IS NOT '' AND last IS NOT '')
BEGIN
THROW SOME ERROR
END
ELSE
INSERT SOMETHING

daytonbrown2 Say Hey Kid Points: 666 More actions · Answer 4

daytonbrown2

Say Hey Kid

Points: 666

October 2, 2024 at 5:15 pm

#4464586

Thanks Ant-Green. Nice to have confirmation.

Steve Collins SSCrazy Points: 2141 More actions · Answer 5

Ant-Green wrote:

You'll need some app / query logic to prevent it.

Or a TRIGGER. This is the sort of situation where one could be appropriate imo

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101060 More actions · Answer 6

I may not fully understand your requirement, but either:

(1) A CHECK constraint

or

(2) an index definition

can be created to do what you want to do.

For example, if you just want to make sure both names aren't blank:

CREATE TABLE #unique_names
(
id INT IDENTITY(1, 1)
,first_name VARCHAR(200) NOT NULL
,last_name VARCHAR(200) NOT NULL
,email_address VARCHAR(254) NOT NULL,
CONSTRAINT unique_names__CK_names_cannot_both_be_blank --<<--
    CHECK( NOT(first_name = '' AND last_name = '') ) --<<--
);

Or, of course, CHECK( first_name <>'' OR last_name <> '' ), whichever you prefer

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

daytonbrown2 Say Hey Kid Points: 666 More actions · Answer 7

Thanks, but both can be blank. I can have a record with a blank name and email address, any unique names and an email address, but not both a unique name and a blank email address. So if you reference the table and data scripts I used at the beginning that should elucidate the problem.

ScottPletcher SSC Guru Points: 101060 More actions · Answer 8

You will need a trigger if you have to reference other rows in the table. A well-written trigger won't hurt your performance that much assuming you have an index available on ( email_address, id ).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ed B Hall of Fame Points: 3651 More actions · Answer 9

daytonbrown2 wrote:

Thanks, but both can be blank. I can have a record with a blank name and email address, any unique names and an email address, but not both a unique name and a blank email address. So if you reference the table and data scripts I used at the beginning that should elucidate the problem.

The example shows an attempt to insert an email with blank names when it already exists with valid names. The desired outcome is clear, you want to prevent the insert that will cause the "bad data" scenario.

What do you want to happen if the reverse occurs? The email exists with blank names and you need to insert the same email with valid names, creating the same "bad data" scenario. Do you want to prevent the insert? effectively prioritising blank names? delete the existing row? or maybe update the existing row? If the answer is update, could you ever have multiple rows to insert with the same email address and need to choose one for the update?

daytonbrown2 Say Hey Kid Points: 666 More actions · Answer 10

In that case, the record will be updated with the name. An insert in that case would be the wrong logic and thus should fail based on the constraint I was trying to create.

Honestly, the logic is pretty simple.(I should have put this at the top actually)


DECLARE @first_name    VARCHAR(100) = 'John'
       ,@last_name     VARCHAR(100) = 'Schmo'
       ,@email_address VARCHAR(254) = 'email_address_no_name@bad_domain.com';

UPDATE #unique_names
SET first_name = @first_name
   ,last_name = @last_name
WHERE email_address = @email_address
      AND @first_name <> ''
      AND @last_name <> ''
  AND last_name='' AND first_name='';

INSERT INTO #unique_names (first_name, last_name, email_address)
SELECT @first_name
      ,@last_name
      ,@email_address
WHERE (
          @first_name = ''
          AND @last_name = ''
          AND NOT EXISTS (SELECT 1 FROM #unique_names AS un WHERE @email_address = un.email_address)
      )
      OR
       (
           @first_name <> ''
           AND @last_name <> ''
           AND NOT EXISTS
    (
        SELECT 1
        FROM #unique_names AS un
        WHERE @email_address = un.email_address
              AND @last_name = last_name
              AND @first_name = first_name
    )
       );

I've just learned that it

Steve Jones - SSC Editor SSC Guru Points: 735897 More actions · Answer 11

I'd likely use a trigger here, with good error handling to let the user know the issue.

daytonbrown2 Say Hey Kid Points: 666 More actions · Answer 12

Yep. That's what I'm doing. Thanks to all for the good advice.