Home / Questions / 74788
Accepted
German AT
Asked: 2020-06-01 10:51:18 +0800 CST 2020-06-01 10:51:18 +0800 CST

How to define a value as unique using the CHECK constraint?

  • 772

I have a table with fields of PROSPECTS such as name, address, telephone etc...

One of the fields is the CURP and I want this column to contain unique data. The problem arises in that the records of prospects in this table are natural and legal persons, so not all of them have CURP, so the UNIQUE restriction does not work for me because although it accepts null values, it only accepts a null value in the entire column .

It occurs to me that I could define a condition with the CHECK constraint but I haven't found how to compare it with the other records in the table, only with the record that is entered.

Is it possible to generate this validation?

sql

2 Answers

  1. Best Answer

    This is relatively easy to achieve using filtered indexes:

    CREATE UNIQUE INDEX IX_Curp ON dbo.Prospectos(Curp) WHERE Curp IS NOT NULL;
    • 2

  2. I get the impression that it can't. What yes, you can make a TRIGGER that validates the data, something like this:

    CREATE TRIGGER PROSPECTOS_INSERT ON PROSPECTOS FOR INSERT 
AS
BEGIN

    DECLARE @errmsg     VARCHAR(255)

    IF UPDATE(CURP)
    BEGIN

        IF EXISTS ( SELECT 1
                FROM INSERTED P
                INNER JOIN PROSPECTOS I
                    ON I.CURP = I.CURP
                WHERE   P.CURP IS NOT NULL
            )
        BEGIN

            SELECT  @errmsg = 'Alguno de los prospectos insertados tiene un CURP que ya existe'
            GOTO ERROR

        END

    END
    RETURN

ERROR:
    RAISERROR( @errmsg, 16, 1)

END
    • CURP should be index to not "kill" the inserts/updates
    • In theory this example should work also in the case of UPDATE
    • 1