• Register
0 votes
115 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

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 99 views
99 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 Martin K 6.6k points
1 vote
2 answers 37 views
37 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 stewart 4k points
1 vote
1 answer 443 views
443 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 57 views
57 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 Martin K 6.6k points
0 votes
1 answer 19 views
19 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 Raphael Pacheco 4.9k points