Unable to create relationship.

Appears when a new constraint has failed on existing data.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Data exists in table '[table name]', database '[database name]', that violates FOREIGN KEY constraint '[constraint name]' being added. ALTER command has been aborted.

[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to create constraint. See previous errors.

Explanation

Existing data fails the foreign key constraint.

Action

• Change the data that fails the foreign key constraint by running a query to show all the foreign key values that do not match primary key values. For example, to find foreign key values in the job_id column of the employee table that do not match primary key values in the jobs table, run a query with this Transact-SQL syntax:

  SELECT employee.emp_id, employee.job_id
  FROM employee LEFT OUTER JOIN jobs ON employee.job_id = jobs.job_id
  WHERE (jobs.job_id IS NULL)
  

• Clear the Check existing data on creation check box in the Relationships property page.

See Also

Constraints

Database Designer

Filtering Rows with WHERE and HAVING

Query Fundamentals