Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Check constraint naming conflicts between tables

    I have created a number of tables (forming an inheritance structure) with the folowing structure
    Code:
    CREATE TABLE aTableA(
       ...
       TypeId INT  NOT NULL DEFAULT 1
          CONSTRAINT CC_TypeId CHECK (TypeId = 1), 
       ...
    )
    
    CREATE TABLE myTable(
       ...
       TypeId INT  NOT NULL DEFAULT 2
          CONSTRAINT CC_TypeId CHECK (TypeId = 2), 
       ...
    )
    
    CREATE TABLE TableQ(
       ...
       TypeId INT  NOT NULL DEFAULT 3
          CONSTRAINT CC_TypeId CHECK (TypeId = 3), 
       ...
    )
    They all contain a column TypeId that identifies the type of data they store. They are FK's to a lookup table that stores parameters that indicate which rules apply for that type of data.

    I have created and dropped these tables often during development. From today on, when I tried to recreate those tables, I got this error message
    Code:
    Msg 2714, Level 16, State 4, Line 6
    There is already an object named 'TypeId' in the database.
    Msg 1750, Level 16, State 0, Line 6
    Could not create constraint. See previous errors.
    Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37
    Object is invalid. Extended properties are not permitted on 'dbo.myTable', or the object does not exist.
    Msg 1088, Level 16, State 12, Line 2
    Cannot find the object "dbo.myTable" because it does not exist or you do not have permissions.
    In BOL I found
    Code:
     constraint_name
       Is the name of a constraint. 
       Constraint names must be unique within the schema to
       which the table belongs.
    But then I should have had these error messages from the very first day I wrote them, weeks ago. I have given them unique names, but I still don't get what has happened and is now causing these errors?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can't explain why you've not had the problem before unless you've changed something else. I've never (knowingly) had two constraints of the same created without error. I'v had the error many times when I've copied & pasted and fogotten to edit the name. BTW - your code wouldn't produce that exact error message ya?

    In any case, I always include the name of the table in the constraint name to limit uniqueness scope to the table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you for your response.
    BTW - your code wouldn't produce that exact error message ya?
    I have changed the names of the tables and the constraint name (the rest of the error messages I have left untouched). I thought I had changed them all. Not good enough so it seems
    I have looked again and I can't find any anomalies, you must have eagle eyes!

    In any case, I always include the name of the table in the constraint name to limit uniqueness scope to the table.
    OK, I'll do so too.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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