:::: MENU ::::

Posts Categorized / Constraints and Keys

  • Dec 17 / 2008
  • 0
Constraints and Keys, dbDigger, Indexes, XML

Cannot create primary xml index ‘%.*ls’ on ‘%.*ls’ because PRIMARY KEY constraint contains column(s) of type timestamp.

The error message
Msg 2735, Level 16, State 201, Line 1
Cannot create primary xml index ‘%.*ls’ on ‘%.*ls’ because PRIMARY KEY constraint contains column(s) of type timestamp.

is a run time error message of severity level 16. This error message appears when you try to create a XML index on a table for which the PRIMARY KEY constraint includes a column of the TIMESTAMP data type. To resolve the error make sure that the XML index is not created on the table as long as the PRIMARY KEY constraint contains the TIMESTAMP column.

  • Dec 11 / 2008
  • 0
Constraints and Keys, dbDigger

Cannot insert duplicate key row in object ‘%.*ls’ with unique index ‘%.*ls’.

The error message
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘%.*ls’ with unique index ‘%.*ls’.

is a run time error of severity level 14. This error message appears when you try to insert an already existing value into a unique key column. Duplicate key values are not allowed in a unique key column. The data that you want to insert is prepresent, hence it can not be inserted.

  • Dec 02 / 2008
  • 0
Constraints and Keys, dbDigger

Cross-database foreign key references are not supported. Foreign key ‘%.*ls’.

The error message
Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key ‘%.*ls’.

is a run time error message of severity level 16. It can occur for all versions of SQL Server. This error message appears when you try to create a FOREIGN KEY constraint that references a table in another database. As errors of Severity Level 16 are generated and can be corrected by the user. To execute the statement make sure that FOREIGN KEY constraints does not reference tables in other databases. You need to enforce RI on another level, such as triggers when two databases are involved.

  • Nov 29 / 2008
  • 0
Constraints and Keys, dbDigger

Skipping FOREIGN KEY constraint ‘%.*ls’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

The error message
Skipping FOREIGN KEY constraint ‘%.*ls’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

is a run time error message for all versions of SQL Server. Query will be executed but the part mentioned in the message will be left out. This message appears if we try to create a FOREIGN KEY constraint for a temporary table. To resolve the error message exclude the statement for creating FOREIGN KEY constraint for a temporary table. It would not cause the statement to stop. But not all commands in your statement have been executed.

  • Nov 27 / 2008
  • 0
Constraints and Keys, dbDigger

Could not create constraint. See previous errors.

The error message
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

is a run time error message of severity level 16. It can be generated at all versions of SQL Server. This error message appears when as a result of a previous error a constraint cannot be created. As errors of Severity Level 16 are generated and can be corrected by the user. The root cause for the previous error must be corrected so that the constraint can be created successfully.

  • Nov 17 / 2008
  • 0
Constraints and Keys, dbDigger

DML statements affected by the IGNORE_DUP_KEY = ON index option

Question: Which DML statements are affected by the IGNORE_DUP_KEY = ON index option in SQL Server 2008?

Answer: INSERT only

Explanation: The IGNORE_DUP_KEY option controls what happens if execution of a multi-row INSERT causes violation of uniqueness in an index. Default behaviour is to give an error message and undo the entire INSERT statement, but with IGNORE_DUP_KEY on, a warning is given and only the offending rows are refused and other rows are still inserted. The DELETE statement is of course not affected, since deleting rows can never cause violations of a unique index. The UPDATE statement could have been affected, but Microsoft has chosen not to do so. The MERGE statement is also unaffected by this option, though this is not documented very well, and I suspect this to be a bug.

The code below demonstrates most of the statements made here:

 CREATE TABLE Src  
 (ID int NOT NULL PRIMARY KEY,  
 Value varchar(30) NOT NULL  
 )  


 CREATE TABLE Dest  
 (ID int NOT NULL,  
 Value varchar(30) NOT NULL  
 )  


 CREATE UNIQUE INDEX ix_Dest  
 ON Dest(ID) WITH (IGNORE_DUP_KEY = ON);  
 go  


 -- Load some sample data  
 INSERT INTO Dest (ID, Value)  
 VALUES (1, 'Existing'),(3,'Also existing');  
 INSERT INTO Src (ID, Value)  
 VALUES (1, 'Added'),(2, 'New value'),(2, 'Another new value');  


 -- Test to see that MERGE does not respect IGNORE_DUP_KEY  
 MERGE INTO Dest  
 USING Src ON Src.ID = Dest.ID  
 WHEN NOT MATCHED  
 THEN INSERT (ID, Value) VALUES (Src.ID, Src.Value);  


 -- Show results  
 SELECT * FROM Dest;  


 -- Test to see that UPDATE is not affected by IGNORE_DUP_KEY  
 UPDATE Dest  
 SET ID = 3,  
 Value = 'Updated'; 

 
 SELECT *  
 FROM Dest;  


 -- Clean up  
 DROP TABLE Dest, Src;  
 GO  

Note: The Question is taken from SQLServercentral.com.

Pages:123
Consult us to explore the Databases. Contact us