-1

How does Microsoft SQL Server Management Studio name the constraint for a unique key?

When I run the following sql command

INSERT INTO alert_template (object_type_id, object_id, alert_object_type_id, alert_object_id, monitor_type_bit_flags, name)
 VALUES (5, null, 5, 0, 2147483647, 'alert-template-system-default');

I got the error message

Violation of UNIQUE KEY constraint 'u_alert_template_object_association'. Cannot insert duplicate key in object 'dbo.alert_template'.

Which unique key does the error message refer to?

I didn't find a constraint named u_alert_template_object_association in the table dbo.alert_template. Was the constraint created implicitly by SQL Server Management Studio? If yes, what is the convention that it uses to name the constraint?

Thanks.

updated

I just found the statement that created the table defines the constraint u_alert_template_object_association. But i didn't find the constraint in SQL management studio's Object Explorer window, where the Constraints under dbo.alert_template doesn't list the constraint u_alert_template_object_association, which was the cause of my confusion in my post. So is the Object Exploere window not supposed to show a constraint defined in the create statement of a table?

CREATE TABLE alert_template
(
        id INT PRIMARY KEY IDENTITY,
        object_type_id TINYINT NOT NULL REFERENCES object_type(id)
                CONSTRAINT u_alert_template_object_type
                CHECK (object_type_id = 5 or object_type_id = 17 or object_type_id =  18 or object_type_id = 26 or object_type_id = 27),
        object_id BIGINT NULL,
        alert_object_type_id TINYINT NOT NULL REFERENCES object_type(id)
                CONSTRAINT u_alert_template_alert_object_type
                CHECK (alert_object_type_id = 5 or alert_object_type_id = 37 or alert_object_type_id = 38),
        alert_object_id TINYINT NOT NULL,
        name NVARCHAR(128) NOT NULL
                CONSTRAINT u_alert_template_name UNIQUE (object_type_id, object_id, name),
        monitor_type_bit_flags INT NOT NULL
                CONSTRAINT u_alert_template_object_association UNIQUE (object_type_id, object_id, alert_object_type_id, alert_object_id, monitor_type_bit_flags),
);

enter image description here

Tim
  • 1
  • 141
  • 372
  • 590
  • 1
    Can you post your create table statement? – McNets Mar 22 '17 at 21:55
  • I just found the statement that created the table, and it defines the constraint `u_alert_template_object_association`. But i didn't find the constraint in SQL management studio's Object Explorer window, where the `Constraints` under `dbo.alert_template` doesn't have teh constraint `u_alert_template_object_association`, which was the cause of my confusion in my post. – Tim Mar 22 '17 at 22:36
  • is it the primary key? – McNets Mar 22 '17 at 22:38
  • The attribute which the constraint `u_alert_template_object_association` applies to is not the primary key, but declared as unique either. – Tim Mar 22 '17 at 22:42
  • There could be a trigger on your table meaning the constraint might be in a different table. It could be a unique index. – Nick.Mc Mar 22 '17 at 22:59
  • @mcnets: See my update with create statement and screenshot of the Object Explorer window. – Tim Mar 22 '17 at 22:59
  • Unique constraints are listed under indexes https://gyazo.com/062a6bea46fff2d765aa697e2060402e . Default and Check constraints are listed under Constraints. Foreign Key constraints are listed under Keys (unique indexes are also here). – SqlZim Mar 22 '17 at 23:02
  • @SqlZim: Thanks. What does "Indexes" mean literally? Why does it have unique constraints? – Tim Mar 22 '17 at 23:03
  • All unique constraints have a unique index to facilitate them, and they can also be used as Keys, so you can find them in both places. – SqlZim Mar 22 '17 at 23:05
  • Thanks. Besides unique constraints, what do "Indexes" and "keys" have respectively? What do they mean literally? @SqlZim – Tim Mar 22 '17 at 23:07
  • I'm not really sure I understand the question. Are you familiar with what an indexes are, and what keys are in relational databases? I'm not trying to be critical, I'm just not sure what you mean by "What do they mean literally?". – SqlZim Mar 22 '17 at 23:13
  • A key is an attribute, and a index is the data structure which implements the search operation. Am i correct? @SqlZim – Tim Mar 22 '17 at 23:20
  • Close enough, sure. – SqlZim Mar 22 '17 at 23:23

1 Answers1

1

This error is informing you that you are trying to enter a duplicate value into a column that has a unique Index/Key defined.

To my knowledge SQL management studio does not create these for you, you have to create them yourself, however, it does have a go at creating a name for you when you do which is usually in the format of IX_xxxxx

Have a look at this article which walks you through creating an index using SQL management studio, you should be able to use this same process to see any Indexes/Keys assigned to the table in question and set you down the path to understanding why your insert is failing.

Add unique constraint in SQL SQL Server 2008 GUI?

Hope this helps!

Community
  • 1
  • 1
jcnewman83
  • 189
  • 1
  • 12