Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'Constraint Name'.
The conflict occurred in database 'Database Name', table 'Table Name', column 'Column Name'.

To illustrate, let’s say you have the following table definitions:

CREATE TABLE [dbo].[Currency] (
    [Code]    CHAR(3) NOT NULL PRIMARY KEY,
    [Name]    VARCHAR(50)
)

CREATE TABLE [dbo].[Currency Conversion] (
    [FromCurrencyCode]    CHAR(3),
    [ToCurrencyCode]      CHAR(3),
    [Conversion]          MONEY
)

And here’s some sample data from these tables:

[dbo].[Currency]
Code Name                                               
---- ----------------------
EUR  Euro
GBP  United Kingdom Pounds
USD  United States Dollar

[dbo].[Currency Conversion]
FromCurrencyCode ToCurrencyCode Conversion            
---------------- -------------- --------------------- 
GBP              USD            1.7508
EUR              USD            1.2107
USD              CAD            1.1482
USD              GBP            .5711
USD              EUR            .8261
USD              AUD            1.3680

You’ve decided to create a FOREIGN KEY relationship between the [FromCurrencyCode] and [ToCurrencyCode] columns of the [dbo].[Currency Conversion] table with the [dbo].[Currency].[Code] column.

ALTER TABLE [dbo].[Currency Conversion]
ADD CONSTRAINT FK_FromCurrencyCode_Currency_Code FOREIGN KEY ( [FromCurrencyCode] )
REFERENCES [dbo].[Currency] ( [Code] )

ALTER TABLE [dbo].[Currency Conversion]
ADD CONSTRAINT FK_ToCurrencyCode_Currency_Code FOREIGN KEY ( [ToCurrencyCode] )
REFERENCES [dbo].[Currency] ( [Code] )

No error is generated by the first FOREIGN KEY constraint on the FromCurrencyCode column since all the values in that column exist in the [dbo].[Currency] table.  However the following error is generated by the second FOREIGN KEY constraint on the ToCurrencyCode column because there are values in that column that does not exist in the [dbo].[Currency] table, such as the CAD (Canadian Dollars) and AUD (Australian Dollars):

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 
'FK_ToCurrencyCode_Currency_Code'.  The conflict occurred in database 'SQLServerHelper',
table 'Currency', column 'Code'.

Solution/Workaround:

When creating a FOREIGN KEY relationship between two tables, make sure that the column values from the second table exists in the column designated as the PRIMARY KEY on the primary table.  To identify column values that do not exist in the primary table, you can do something like the following:

SELECT DISTINCT [FromCurrencyCode] 
FROM [dbo].[Currency Conversion] CC
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] Curr
                  WHERE CC.[FromCurrencyCode] = Curr.[Code])

SELECT DISTINCT [ToCurrencyCode] 
FROM [dbo].[Currency Conversion] CC
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] Curr
                  WHERE CC.[ToCurrencyCode] = Curr.[Code])

This will identify the currency codes that are not in the [dbo].[Currency] table.  Once you’ve identified those missing column values, insert them in the primary table and then create the FOREIGN KEY constraint.

Server: Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.

The simplest way reproduce the error is with the following script:

DECLARE @DecimalValue DECIMAL(4,2)
SET @DecimalValue = 100.00

Since the precision of the @DecimalValue variable is set to 4 with 2 decimal places, the highest value it can have is 99.99.  Assigning a value that has more than 2 digits on the left of the decimal place, as the example shows, generates the error:

Server: Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.

On the other hand, if you assign a value where there are more decimal places than what the variable can hold, it simply rounds the value and does not generate any error:

DECLARE @DecimalValue DECIMAL(4,2)
SET @DecimalValue = 98.4584
SELECT @DecimalValue -- Returns 98.46

Solution/Workaround:

To avoid this error, always make sure that the precision of the decimal or numeric variable that you are going to use is enough to accommodate the value being assigned to it.  Just by increasing the precision, the error can be avoided:

DECLARE @DecimalValue DECIMAL(5,2)
SET @DecimalValue = 100.00

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Column Name', table 'Table Name';
column does not allow nulls.  INSERT fails.
The statement has been terminated.

To illustrate, let’s say you have the following table definitions:

CREATE TABLE [dbo].[Users] (
    [UserName]     VARCHAR(10)  NOT NULL,
    [FullName]     VARCHAR(100) NOT NULL,
    [Email]        VARCHAR(100) NOT NULL,
    [Password]     VARCHAR(20)  NOT NULL,
    [CreationDate] DATETIME     NOT NULL DEFAULT(GETDATE())
)

There are three ways that the error can be encountered.  The first way is when a column is not specified as one of the columns in the INSERT clause and that column does not accept NULL values.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com' )

Given this INSERT statement, the [Password] column is not specified in the column list of the INSERT INTO clause.  Since it is not specified, it is assigned a value of NULL. But since the column does not allow NULL values, the following error is encountered:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Password', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

The second way that the error can be encountered is when a NULL value is explicitly assigned to the column that does not allow NULLs.

INSERT INTO [dbo].[Users]([UserName], [FullName], [Email], [Password])
VALUES ( 'MICKEY', 'Mickey Mouse', NULL, 'Minnie' )

As can be seen from this INSERT command, the [Email] column is being assigned a NULL value during the insert but since the column does not allow NULL values, the following error is generated:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Email', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

The third possible way that the error is encountered is similar to the second one, which is by explicitly assigning a NULL value to a column, as shown below:

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', NULL )

The only difference is that the column being assigned to has a default value (in this case, the default value of the [CreationDate] column is current system date and time as generated by the GETDATE() function).  Since the column has a default value, you would think that if a NULL value is assigned to it, it will assign the default value instead. However, the following error is encountered:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CreationDate', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Solution/Workaround:

Regardless of the way on how the error is encountered, if a column does not accept NULL values, always assign a value to it when inserting new records to the table.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', GETDATE() )

If the column does not accept NULL values but has a default value assigned to it and you want that the default value be used for the newly inserted record, just do not include that column in the INSERT statement and the default will automatically be assigned to the column.

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie' )

In this example, since the [CreationDate] column has a default value of GETDATE(), since it is not included in the column list in the INSERT INTO clause, the default value gets assigned to the column.

Another way of explicitly assigning the default value is by using the reserved word DEFAULT in the VALUES list, as can be seen in the following:

INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', DEFAULT )

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'Table Name' 
that match the referencing column list in the foreign key 'Foreign Key Constraint Name'.

To illustrate, let’s say you have the following table definition:

CREATE TABLE [dbo].[Department] (
    [DepartmentID]      INT NOT NULL IDENTITY,
    [DepartmentName]    VARCHAR(50)
)
GO

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]     INT NOT NULL IDENTITY,
    [FirstName]      VARCHAR(50),
    [LastName]       VARCHAR(50),
    [DepartmentID]   INT
)

Based on the business requirements, an employee can only belong to one department.  To make sure that the DepartmentID assigned to the employee exists in the [dbo].[Department] table, you create a FOREIGN KEY constraint on the column:

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )

But since the DepartmentID in the [dbo].[Department] is not designated as a PRIMARY KEY on that table, the following error is encountered:

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'dbo.Department'
that match the referencing column list in the foreign key 'FK_Employee_Department'.

Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Solution/Workaround:

To avoid this error, you have to first create the PRIMARY KEY constraint in the table to be referenced in a FOREIGN KEY constraint.

ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [PK_Department] PRIMARY KEY ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

If the table to be referenced by the FOREIGN KEY constraint already has a PRIMARY KEY and it’s not the column to be referenced by the FOREIGN KEY, you can create a UNIQUE index or UNIQUE constraint on the column.

Using a UNIQUE index:

CREATE UNIQUE INDEX [IX_DepartmentID]
ON [dbo].[Department] ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

Using a UNIQUE constraint:

ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [IX_DepartmentID] UNIQUE ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

Server: Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'Table Name'.
Drop the existing clustered index 'Clustered Index Name' before creating another.

As an example similar to a telephone directory, let’s say you have the following table definition:

CREATE TABLE [dbo].[Directory] (
    [LastName]    VARCHAR(50) NOT NULL,
    [FirstName]   VARCHAR(50) NOT NULL,
    [AreaCode]    CHAR(3),
    [PhoneNumber] CHAR(7)
)
GO

ALTER TABLE [dbo].[Directory]
ADD CONSTRAINT PK_Directory PRIMARY KEY ( [LastName], [FirstName] )
GO

This creates a PRIMARY KEY constraint on the LastName and FirstName columns, which by default is CLUSTERED.  Creating another clustered index on the AreaCode and PhoneNumber generates the error:

CREATE CLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]
ON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )
GO

Server: Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'Directory'.
Drop the existing clustered index 'PK_Directory' before creating another.

Solution/Workaround:

Since a table can only have one clustered index, you have to create any additional indexes on the table as non-clustered.

CREATE NONCLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]
ON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )
GO

If you want to change how the table is clustered, you have to drop the existing clustered index first before you can create a new one.

ALTER TABLE [dbo].[Directory]
DROP CONSTRAINT [PK_Directory]
GO

CREATE CLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]
ON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )
GO

ALTER TABLE [dbo].[Directory]
ADD CONSTRAINT [PK_Directory] PRIMARY KEY ( [LastName], [FirstName] )
GO

Since the [dbo].[Directory] already have a clustered index, the PRIMARY KEY constraint defaults to NONCLUSTERED.