• Register
0 votes
439 views

Problem :

I am trying to create the table but facing below error

“Error: There is no unique constraint matching given keys for referenced table.”

How to fix the above error?

7 5 2
3,870 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

When you do the UNIQUE as the table level constraint as you have done then what your defining is the bit like the composite primary key see here is what it meams.

"This specifies that a *combination* of values in a indicated columns is unique across a whole table, though any one of a columns need not be (and ordinarily isn't) the unique."

This clearly means that either field could possibly have the non unique value provided a combination is unique and this does not match the foreign key constraint.

Most likely you want a constraint to be at a column level. So instead of define them as table level constraints, 'append' the UNIQUE to the end of a column definition like name VARCHAR(60) NOT NULL UNIQUE or specify it in individual table level constraints for each field.

OR

 In a postgresql all the foreign keys must reference the unique key in a parent table, so in your xyz table you must have the unique (name) index.

I hope above solutions help you in resolving your error.

9 7 4
38,600 points
0 votes

Solution:

It's since the name column on the bar table does not have the UNIQUE constraint.

So conceive you have 2 rows on the bar table that bearing the name 'ams' and you insert a row on baz with 'ams' on bar_fk, which row on bar would it be referring because there are two rows matching?

In postgresql all foreign keys should reference a unique key in the parent table, thus in your bar table you should have a unique (name) index.

At the time you do UNIQUE as a table level constraint as you have done then what your defining is a bit like a multiple primary key see ddl constraints, here is an extract

"This specifies that the *combination* of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique."

This refers that either field could probably have a non unique value given the combination is unique and this does not match your foreign key constraint.

Possibly you want the constraint to be at column level. so rather then idebtify them as table level constraints, 'append' UNIQUE to the end of the column definition like name VARCHAR(60) NOT NULL UNIQUE or specify particular table level constraints for each field.

You must have name column as a unique constraint. here is a 3 lines of code to alter your issues

  1. First trace out the primary key constraints by typing this code
\d table_name

you are displayed like this at bottom "some_constraint" PRIMARY KEY, btree (column)

  1. Drop the constraint:

ALTER TABLE table_name DROP CONSTRAINT some_constraint
  1. Include a new primary key column with existing one:

ALTER TABLE table_name ADD CONSTRAINT some_constraint PRIMARY KEY(COLUMN_NAME1,COLUMN_NAME2);

As the name column on the bar table does not have the UNIQUE constraint.

Let's presume you have 2 rows on the bar table that hold the name 'ams' and you are attempting to insert a row on baz with 'ams' on bar_fk, now which row on the bar would it be referring to because there are two rows matching can be taken care.

10 6 4
31,120 points

Related questions

1 vote
1 answer 216 views
216 views
Problem : I am learning Oracle and trying to create and drop tables. If I try to drop my table it is always showing me below error: DROP TABLE SUBURB; DROP TABLE CITY; DROP TABLE STATE; Error at line 1: ORA-02449: unique/primary keys in table referenced ... already used by an existing object. I am unable to understand the above errors. Can someone guide me in fixing above Oracle related errors?
asked May 12, 2020 Martin K 6.6k points
0 votes
1 answer 4 views
4 views
there are no primary or candidate keys in the referenced table
asked Feb 16 ArifulIslam 7.5k points
1 vote
2 answers 78 views
78 views
Problem : I am very new to database programming. I have recently started learning about databases. Currently I am desperately trying to create the database somehow for a very first time. I have already designed all of my tables. Now I am trying to create their relationships. But ... till now unable to fix it. So I am looking for database expert who can suggest me the quick fix for my above error.
asked Apr 22, 2020 stewart 4k points
1 vote
1 answer 1.2K views
1.2K views
Problem : Getting following error while executing function no function matches the given name and argument types. you might need to add explicit type casts.
asked Nov 11, 2019 peterlaw 6.9k points
1 vote
1 answer 117 views
117 views
Problem: I am trying to learn Oracle. I have created two tables their names are Table Employee and Table USER, here is the structure. CREATE TABLE Employee ( ID NUMBER NOT NULL , USERID NUMBER, CONSTRAINT B_PK PRIMARY KEY ( ID ) ENABLE ); CREATE TABLE USER ( ... above error but because of my elementary knowledge on Oracle I am unable to fix it. Kindly guide me in fixing above Oracle error.
asked May 29, 2020 Martin K 6.6k points
0 votes
1 answer 39 views
39 views
Problem: I am able to create the table named employee which contains the column empid which is a computed column and empid column is the primary key also. It is the self referencing table and the column managerid references back to itself. But now if I try to save ... error my table is still working perfectly. I am trying to work in a table graphically not coding every time I make the change.
asked Jul 4, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 60 views
60 views
Problem: I am new and learning, I need help, can anyone help by filling in the blanks? A field in an Access database table that has no value is determined by Access to have a ____ value? A. custom B. zero C. null D. number
asked Feb 21, 2020 maddi86 5.4k points
0 votes
1 answer 82 views
82 views
Problem : I am trying to import mySql database which was originally an access database into phpmyadmin and its giving me following error: incorrect table definition; there can be only one auto column and it must be defined as a key
asked Nov 14, 2019 peterlaw 6.9k points
0 votes
1 answer 24 views
24 views
Problem: I am unable to understand why a TRUNCATE on mygroup is not working? Even though I have it ON DELETE CASCADE SET I encounter below strange error: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, ...    DateTime     DATETIME DEFAULT NULL, FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,    UNIQUE(GroupID) ) ENGINE=InnoDB;
asked Jul 23, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 353 views
353 views
Problem : I am facing following error while executing script cannot insert explicit value for identity column in table when identity_insert is set to off.
asked Nov 15, 2019 peterlaw 6.9k points