Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Unique Constraint

    What is the simplest way to add a unique constraint on a field of type varchar(7) that can allow any number of <NULL>'s?

    I only want to ensure that when this field is updated, it is updated with a value that has not been used.

    Code:
    IF EXISTS (SELECT Project FROM tbProjects WHERE Project = @cProject)
            RAISERROR('Project number already used!',16,1)
    ELSE
            UPDATE tbProjects SET Project = @cProject WHERE ProjectID = @iProjectID
    GO
    Also, I cannot allow the user to chante the project field value once it is set.


    Any suggestions?

    Mike B
    Last edited by MikeB_2k4; 05-19-04 at 13:41.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope...but you can in DB2

    As far as not allowing a change to a null null value...how about a trigger

    You can use the trigger to mimic the constraint you want...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could to this through a trigger that rolls back the action if the value already exists in the table, or if the value is already set.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    You could to this through a trigger that rolls back the action if the value already exists in the table, or if the value is already set.

    Damn! That's an absolutely OUTSTANDING suggestion blind dude...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Apparently, you were faster on the "Trigger" than I.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Posts
    134
    Do you not have to begin a transaction before you can roll it back? How would you do this? I mean, you would have to begin the transaction int the storedproc, then the trigger may or may not roll it back, then how and where would you commit.

    My appologies if this is a rediculous question!

    Mike B

  7. #7
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59
    Oh come on guys show some imagination!!!

    It can be done but she aint pretty....


    Code:
    create table DodgyConstraint(DC VARCHAR(7) NULL, IDCol INT identity(1,1) NOT NULL,
    	IsUnique AS CASE WHEN DC IS NULL THEN CAST(IDCol AS VARCHAR(7))
    				ELSE DC END, UNIQUE (IsUnique))
    --Insert 2 nulls
    insert DodgyConstraint (DC)
    	Values (NULL)
    insert DodgyConstraint (DC)
    	Values (NULL)
    --Insert a "real" value
    insert DodgyConstraint (DC)
    	Values ('Dave')
    insert DodgyConstraint (DC)
    	Values ('Dingo')
    --TYhis blows
    insert DodgyConstraint (DC)
    	Values ('Dave')
    
    Select * from DodgyConstraint

  8. #8
    Join Date
    May 2004
    Posts
    14
    Internally, SQL Server opens a transaction for each record that's inserted, updated, or deleted. The transaction is committed after all the nitty-gritty details are complete. If you've explicitly opened a transaction, then this update simply gets added to it, waiting for your commit; if not, it's actually finalized in the database.

    How have I seen this? Create a trigger (mine was for insert, update, delete) that has a commit in it. Open a transaction, execute some DML, then try to roll back. You won't be able to, because the trigger's commit operates on the internal transaction, then the "I did everything correctly!" commit from SQL Server commits your transaction.

    If you don't open your own transaction, after the DML statement completes, you'll get '0 record(s) updated' because the commit in the trigger eats the record-count result. (We hit that bug, the '0 records' result really confused one of our apps....)

  9. #9
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by byrmol
    Oh come on guys show some imagination!!!

    It can be done but she aint pretty....


    Code:
    create table DodgyConstraint(DC VARCHAR(7) NULL, IDCol INT identity(1,1) NOT NULL,
    	IsUnique AS CASE WHEN DC IS NULL THEN CAST(IDCol AS VARCHAR(7))
    				ELSE DC END, UNIQUE (IsUnique))
    --Insert 2 nulls
    insert DodgyConstraint (DC)
    	Values (NULL)
    insert DodgyConstraint (DC)
    	Values (NULL)
    --Insert a "real" value
    insert DodgyConstraint (DC)
    	Values ('Dave')
    insert DodgyConstraint (DC)
    	Values ('Dingo')
    --TYhis blows
    insert DodgyConstraint (DC)
    	Values ('Dave')
    
    Select * from DodgyConstraint
    That works, very nice. Thanks.

    Mike B

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    Create FUNCTION [udf_CheckDuplicates] (@UDFC varchar(20))
    RETURNS INT
    AS
    begin
    declare @returnvalue int
    if (@UDFC is null)
    begin 
     select @returnvalue = 0
    end
    else 
    begin 
     select @returnvalue = count(*) from UDFCheck where UDFC is not null and UDFC = @UDFC
    end
    return @returnvalue
    end
    go
    create table UDFCheck 
    (
     UDFC varchar (20) null check (dbo.udf_CheckDuplicates(UDFC) > 0)
    )
    go
    Insert into UDFCheck 
    select 'Enigma'
    go
    Insert into UDFCheck 
    select 'Brett'
    go
    Insert into UDFCheck 
    select 'Blindman'
    go
    Insert into UDFCheck 
    select null
    go
    Insert into UDFCheck 
    select null
    
    go
    Insert into UDFCheck 
    select 'Enigma'
    go
    
    select * from UDFCheck
    go
    drop table UDFCheck
    go
    drop function udf_CheckDuplicates
    go
    Was trying a UDF but is not working .. any idea where the mistake lies ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by byrmol
    It can be done but she aint pretty....
    Code:
    create table DodgyConstraint
    ( DC VARCHAR(7) NULL
    , IDCol INT identity(1,1) NOT NULL
    , IsUnique AS 
        CASE WHEN DC IS NULL 
             THEN CAST(IDCol AS VARCHAR(7))
             ELSE DC END
    , UNIQUE (IsUnique)
    )
    i think that's quite, er, imaginative, and i've added it to my NeatSQL collection

    nice job
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It didn't create the table for me

    I get

    Server: Msg 1934, Level 16, State 1, Line 1
    CREATE TABLE failed because the following SET options have incorrect settings: 'ARITHABORT'.
    Server: Msg 1750, Level 16, State 1, Line 1
    Could not create constraint. See previous errors.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, me too

    Error: CREATE TABLE failed because the following SET options have incorrect settings: 'ARITHABORT'. (State:37000, Native Code: 78E)
    Error: Could not create constraint. See previous errors. (State:37000, Native Code: 6D6)

    this is on MSDE



    i never bothered to test it, i just assumed byrmol = works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    By the way ... any answers to my question ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by Brett Kaiser
    It didn't create the table for me

    I get
    Quote Originally Posted by r937
    i never bothered to test it, i just assumed byrmol = works
    It worked for me!

    Mike B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •