:::: MENU ::::

Posts Categorized / Constraints and Keys

  • Dec 03 / 2013
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, DDL, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Today i was working to create foreign keys on few tables. Some of ADD CONSTRAINT statements got failed by generating the following error message.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “%”. The conflict occurred in database “%”, table “%”, column ‘%’.

If we look closely at the descriptive message it points to the same foreign key constraint that we are just trying to create. Reason is that while creating the constraint SQL Server is trying to validate the existing data based on new constraint. There are some records with no reference in their base primary key table. Such records are cause of error here.

SOLUTION

we have couple of options. If you want to make sure that no such orphaned records be there in subject table you may just detect and delete them. Then ADD CONSTRAINT statement will work without any error. However deleting the data will hardly be a suitable option. Alternate is to use the ADD CONSTRAINT statement with NOCHECK option. It will make sure that existing data is not validated for constraint at time of creation.

WITH CHECK | WITH NOCHECK

Here is some informative text on these options from BOL.

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

QUERY OPTIMIZER BEHAVIOR

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  • Apr 23 / 2011
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, Publications of Atif Shehzad on MSSQLTips.com

Understanding the update and delete rules for SQL Server Foreign Key Relationships

Foreign Key relationships are widely used in SQL Server database design. They provide numerous benefits. However there are some associated issues while applying update/delete statements on parent table .i.e. table with referenced primary key. SQL Server provides rules to manage the effect of operation on child records. It is important to know the use and effects of these rules. Read my recently published article  about Understanding the update and delete rules for SQL Server Foreign Key Relationships.

  • Jun 22 / 2009
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, SSMS tips and tricks, T-SQL Scripts

Create unique key constraint through T-SQL and SSMS

There may be required to make sure that to fulfill a certain business logic, each record should be unique in terms of some columns. To implement this there is no need to create any primary key having combination of columns. Simply a unique key constraint may be created.
To consider an example of creating unique key constraint let us create a sample table

 
USE AdventureWorks  
 GO  
 CREATE TABLE TestUKey(serNumb SMALLINT IDENTITY(1,1),   
 name VARCHAR(50),  
 city VARCHAR(50),  
 phone VARCHAR(13),  
 cellNumb VARCHAR(13))  
 GO  

Suppose now it is required to make sure that each record have unique combined values in name and cellnumb. To implement this constraint we may use following T-SQL statement
[/sql] USE AdventureWorks
GO
ALTER TABLE TestUKey
ADD CONSTRAINT IX_TestUKey_NameCell
UNIQUE(Name, CellNumb)
GO
[/sql]
Our required logic has been implemented and to confirm it following is a part of result from <

sp_help TestUKey

Unique key constraint confirmation

To create unique key constraint through SQL Server Management Studio (SSMS), following are required steps.
Drop existing unique key constraint so that we may create unique key constraint through SSMS

 
USE AdventureWorks  
 GO  
 ALTER TABLE TestUKey  
 DROP CONSTRAINT IX_TestUKey_NameCell  
 GO  

Now we are ready to create unique key constraint through SSMS

  • Right click on table on which constraint will be implemented and go to Design
  • Right click on left bar of design to access indexes and keys or access from tool bar

Create unique key constraint through SSMS 1

  • A frame will appear. Choose both columns and select unique key in type.
  • Close the frame and exit from designer.

Unique key has been created and this may be verified through SSMS

Confirm unique key constraint through SSMS

I have noticed that along with each unique key created a non clustered index is automatically created comprising the columns of unique key.
Click here for another example of unique key constraint implementation.

  • Apr 27 / 2009
  • 2
Constraints and Keys, DBA Interview questions, dbDigger, T-SQL Interview Questions

How to update associated foreign keys when primary key specification is changed

I had to change a auto generated primary key with some meaningful key value. After designing the meaningful key it was now time to consider the impacts on foreign keys related to that specific primary key. I planned to first script all related keys for re creation after the key change operation.
In test environment i just changed the primary key through SSMS to see the impact and surprisingly SSMS asked my permission to change the related foreign keys also. I was glad and task was successfully completed with some clicks only.
To go on with an example let us choose some keys in AdventureWorks
Check primary key to be changed

USE AdventureWorks
GO

— Check Specifications of primary key to be changed
sp_help [HumanResources.Department]
GO

Specification of Primary key to be changed

We can confirm that a primary key [DepartmentID] exists in the table. Data type of [DepartmentID] is smallint. Also we may confirm that this primary key is referenced as a foreign key in table [HumanResources.EmployeeDepartmentHistory].
Now i have to update the primary key [DepartmentID] in HumanResources.Department. For this purpose

  • right click on table
  • go to design
  • In design window change data type of [DepartmentID] from smallInt to Int
  • Just as you click out of modified data type cell, you will be facing following confirmation dialog

Confirmation about data type change

Dialog wants your permission to change data types of associated foreign keys. If you click yes data types of associated foreign keys will be changed and relations will be preserved with new data type. So just save the design changes and get your changes implemented. If you click no data type of primary key will not be changed.
Now confirm the foreign key data type change

USE AdventureWorks
GO

— Check data type change in foreign key
sp_help [HumanResources.EmployeeDepartmentHistory]

In the result we may confirm the change in data type of foreign key in [HumanResources.EmployeeDepartmentHistory].
So you do not have to plan any stretigies for synchronnizing the changes made in any foreign key. SQL Server Management Studio (SSMS) designer will work for you.

  • Apr 21 / 2009
  • 0
Constraints and Keys, dbDigger, T-SQL Scripts

How to Add unique key constraint

Primary key is used to avoid duplication of records in a table. There may be business requirements where single column or group of columns other than primary key are required to be unique. Along with implementing this logic in application code it would be better to implement this constraint at database layer.
To go on with a little example create a test table as

 
USE AdventureWorks  
 GO  
 CREATE TABLE testTable  
 (RecordID SMALLINT IDENTITY(1,1) PRIMARY KEY,  
 courseName VARCHAR(30),  
 startDate DATETIME,  
 endDate DATETIME)  
 GO  

TestTable is now there with total four columns. RecordID is primary key with identity property. Now it is required that there should be unique course name and start date when combined. To implement this logic let us alter our table to add unique key constraint for two columns [recordID] and [startDate]

 
USE AdventureWorks  
 GO  
 ALTER TABLE testTable  
 ADD CONSTRAINT UK_testTable_testinguniquekeys  
 UNIQUE NONCLUSTERED (courseName,startDate)  
 GO  

Now let us check that our constraint is implementing the required logic of unique keys

 
USE AdventureWorks  
 GO  
 INSERT INTO TestTable (courseName,startdate,enddate)  
 VALUES ('Course1','2009-04-21','2009-06-15')  
 GO  
 INSERT INTO TestTable (courseName,startdate,enddate)  
 VALUES ('Course1','2009-04-21','2009-06-20')  
 GO  

First row is inserted successfully but there was following error for second row because courseName and startDate were same as first row.

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint ‘UK_testTable_testinguniquekeys’. Cannot insert duplicate key in object ‘dbo.TestTable’.
The statement has been terminated.

Similarly unique key constraint may be implemented for a single column or for group of columns. Click here for another example of creating unique key constraint through SSMS.

  • Mar 19 / 2009
  • 0
Constraints and Keys, dbDigger, T-SQL Scripts

Bind or Unbind a rule through sp_bind and sp_unbind

RULES are SQL Server objects used to implement restrictions and checks on data in a column. A single created RULE may be bind to many columns. Suppose we have a RULE created. This rules specify that minimum DOB of an employee being entered should always be greater than or equal to 18 years. Now once RULE is created then it can be bind to many columns of date of birth in different tables. And all bind columns will accept date of birth greater than or equal to 18 years of age.
To bind a column DOB in Employees table following is syntax

Use Databasename
GO

EXEC sp_bindrule ‘MinDOB’, ‘Employees.DOB’
GO

Similarly same RULE may be bind to as many columns in a database as required. Now in order to drop a RULE, it should be first unbind from all bind columns. So to unbind MinDOB from [Employees].[DOB] following is the syntax

Use Databasename
GO

EXEC sp_unbindrule ‘Employees.DOB’
GO

For details of creating and using rules and defaults in SQL Server please read my article on MSSQLTips.com

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