What does the error message “there is no unique constraint matching given keys for referenced table” mean in SQL?
What does the error message “there is no unique constraint matching given keys for referenced table” mean in SQL?
Share
Sign Up to our social questions and Answers Engine to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers Engine to ask questions answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
What does the error message “there is no unique constraint matching given keys for referenced table” mean in SQL?
This error message usually means that there is a foreign key referencing a table, and the referenced fields in that table are not part of a unique constraint. In order to reference a table with a foreign key in SQL, the referenced fields must be either the primary key of the table, or part of a unique constraint or unique index. If they’re not, the database won’t be able to ensure referential integrity, because there could be multiple rows that match the foreign key. You would need to alter the table schema to include a unique constraint.
This error message generally means that you’re trying to add a foreign key constraint to a table, but the referenced table does not have a unique constraint or primary key on those columns that you’re referencing.
For instance, if you’re trying to add a foreign key in table A that references table B, SQL will throw this error if there isn’t a unique or primary key in B for the columns that your foreign key references.
In database terminology, a foreign key in one table points to a primary key in another table. The primary key is unique and identifies a specific row in table B. The foreign key in table A should uniquely identify a row of table B.
So, you’ll get rid of this error message by ensuring that the columns in table B (that you’re referencing) are either the primary key or have a unique constraint set on them.
Please review your schema again to confirm that the unique or primary key constraint exists on columns you are trying to reference with a foreign key in another table. To create a unique constraint you can use the `UNIQUE` keyword when creating or modifying a table. If there’s no definite unique key in your data, you may need to reassess your data model.
This error generally mean that the column in the table you trying to reference does not have a unique constraint. Basically, you need to make sure that every piece of data in the column is unique, like every person has a unique social security number, that kind of thing. Then SQL will know which one is it supposed to reference.
This error means that you’re trying to reference a table in SQL without a unique key (like an ID), which you need to properly identify individual rows.