Post reply

Constraint definition

  • December 12, 2005 at 10:35 pm

    #92689

    All

    It's been a hectic day and my mind is already on they way home.

    I need to define a unique index/constraint to function as per the comments in my test code. It's our system so we're not constrained in terms of table design changes, adding additional tables, etc...

    SET NOCOUNT ON
    CREATE TABLE #codes (
 id int IDENTITY(1, 1 )
 , code varchar( 3 )
 , email varchar( 20 )
 , oldemail varchar( 20 )


) ;
    INSERT INTO #codes VALUES ( '492', 'jill@here.net', '' );
INSERT INTO #codes VALUES ( '492', 'peter@here.net', '' );
INSERT INTO #codes VALUES ( '493', 'john@here.net', '' );
INSERT INTO #codes VALUES ( '493', 'mary@here.net', '' );
INSERT INTO #codes VALUES ( '495', 'bin@here.net', '' );
INSERT INTO #codes VALUES ( '495', 'john@here.net', '' );
    SELECT * FROM #codes
    -- This should pass because it's the generic address
    UPDATE #codes SET email = 'bin@here.net', oldemail = email WHERE id = 6
    -- This should fail because it's a duplicate code and address
    INSERT INTO #codes VALUES ( '492', 'jill@here.net', '' )
    SELECT * FROM #codes
    DROP TABLE #codes

     

    Anyone got any ideas?

     

    --------------------
    Colt 45 - the original point and click interface

  • December 12, 2005 at 11:32 pm

    #609138

    Hi...

    How that will fail because of IDENTITY property.......

     

    Regards,

    Papillon

  • December 12, 2005 at 11:36 pm

    #609139

    It won't fail ... that is the issue. I need to apply a business rule that the same address cannot be assigned to the same code unless its the generic "bin" address.

    So in my test code the insert should fail, but currently it doesn't.

     

    --------------------
    Colt 45 - the original point and click interface

  • December 13, 2005 at 1:31 am

    #609143

    You could use a computed column, and put a unique index on that.

    Populate the computed column with a CASE statement that sets it to either the email address, or the primary key if the email address is the default, e.g. 

    CREATE TABLE #codes ( id int IDENTITY(1, 1 ) 
, code varchar( 3 ) 
, email varchar( 20 ) 
, oldemail varchar( 20 )
, emailcheck AS CASE email 
            WHEN  'bin@here.net' THEN convert (varchar(20),id)
            ELSE email
            END
) 
GO
CREATE UNIQUE INDEX test1 on #codes(emailcheck)

  • December 14, 2005 at 2:10 am

    #609406

    You could add:

    CREATE UNIQUE index Codes_AK ON #codes (code, email)

    ALTER TABLE #codes ADD CONSTRAINT Codes_UC UNIQUE (code, email)

    However both of your insert statements will fail:

    -- This should pass because it's the generic address

    UPDATE #codes SET email = 'bin@here.net', oldemail = email WHERE id = 6

    Will fail because id=5 has already used the code and email values

    -- This should fail because it's a duplicate code and email values

    INSERT INTO #codes VALUES ( '492', 'jill@here.net', '' )

    Will fail because id=1 has already used the code and email values

    So I guess you were not looking for a unique constraint, but a check constraint like Ian provided.

    Andy

  • December 14, 2005 at 6:48 am

    #609443

    This is a problem in logical database design. In a logical design, surrogate keys (identity) should not be used as they are a physical implimentation.

    Business rules:

    1. There is a generic email account.

    2. Codes are uniquely identified by a Code and a CodeQualifier. The column CodeQualifier is necessary otherwise there is no natural key.

    3. Each Codes can either use the generic email account or a specific email account.

    4. When a code has a specific email account, the email account must be unique within a code.

    The resulting schema is:

    create schema authorization dbo

    create table EmailGenericOOAK -- One Of A Kind

    ( EmailGenericIdtinyint not null

    , Email varchar( 20 )not null

    -- These constraints insure that the table may only have one row

    , constraint EmailGeneric_P primary key (EmailGenericId)

    , constraint EmailGeneric_C_EmailGenericId CHECK (EmailGenericId = 1)

    )

    CREATE TABLE Codes

    ( Code varchar( 3 ) not null

    , CodeQualifier varchar( 3 ) not null

    , EmailGeneric char(1) not null

    , constraint Codes_P primary key (Code, CodeQualifier )

    , constraint Codes_C_EmailGeneric CHECK (EmailGeneric in ('Y','N'))

    )

    Create Table CodesEmails

    ( Code varchar( 3 ) not null

    , CodeQualifier varchar( 3 ) not null

    , AsOfTsdatetimedefault current_timestamp not null

    , Email varchar( 20 )not null

    , constraint CodesEmails_P primary key (Code,CodeQualifier,AsOfTs )

    , constraint CodesEmails_U_Email unique (Code, Email)

    , constraint Codes_F_CodesEmails foreign key (Code,CodeQualifier) references Codes

    )

    go

    -- Add some test data

    insert into EmailGenericOOAK

    (EmailGenericId, Email )

    values (1, 'bin@here.net')

    go

    begin transaction

    -- Generic email

    insert into Codes (code , CodeQualifier,EmailGeneric) VALUES ( '400','A','Y' )

    -- Specific emails

    insert into Codes (code , CodeQualifier,EmailGeneric) VALUES ( '492','A','N' )

    insert into Codes (code , CodeQualifier,EmailGeneric) VALUES ( '492','B','N' )

    insert into Codes (code , CodeQualifier,EmailGeneric) VALUES ( '493','A','N' )

    insert into Codes (code , CodeQualifier,EmailGeneric) VALUES ( '493','B','N' )

    insert into CodesEmails (code , CodeQualifier, Email) values ('492','A', 'peter@here.net')

    insert into CodesEmails (code , CodeQualifier, Email) values ('492','B', 'john@here.net')

    insert into CodesEmails (code , CodeQualifier, Email) values ('493','A', 'peter@here.net')

    insert into CodesEmails (code , CodeQualifier, Email) values ('493','B', 'john@here.net')

    commit

    go

    select Codes.code , Codes.CodeQualifier

    ,CASE Codes.EmailGeneric

    WHEN 'Y' THEN EmailGenericOOAK.Email

    else CodesEmails.Email

    END as Email

    FROMEmailGenericOOAK

    ,Codes

    LEFT OUTER JOIN CodesEmails

    on CodesEmails.Code= Codes.Code

    and CodesEmails.CodeQualifier= Codes.CodeQualifier

    and Codes.EmailGeneric = 'N'

    go

    begin transaction

    insert into Codes (code , CodeQualifier,EmailGeneric) VALUES ( '492','C','N' )

    -- Test for rule 4. When a code has a specific email account, the email account must be unique within a code.

    -- This should fail as Peter's email already is referenced under code 492

    insert into CodesEmails (code , CodeQualifier, Email) values ('492','C', 'peter@here.net')

    SQL = Scarcely Qualifies as a Language

  • December 15, 2005 at 3:09 pm

    #609904

    All

    Fixed this by using a simple trigger.

    CREATE TRIGGER trg_codes 
ON dbo.codes 
FOR UPDATE, INSERT
AS
 IF EXISTS (
   SELECT ext.[ID]
   FROM inserted ins
    INNER JOIN [dbo].[codes] ext
    ON ext.[Code] = ins.[Code]
    AND ext.[EMail] = ins.[EMail]
    AND ext.[ID] <> ins.[ID]
   WHERE ins.[EMail] <> 'bin'
    AND ins.[EMail] <> 'bin@here.net'
 &nbsp 
 BEGIN
  ROLLBACK TRANSACTION
  RAISERROR('Duplicate code and email addresses are not allowed.', 16, 1) WITH LOG
 END

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 7 posts - 1 through 6 (of 6 total)

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