Export (0) Print
Expand All

Unable to create relationship.

SQL Server 2000

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft