Tech Tip: ORA-02270: no matching unique or primary key for this column-list ORA-06512: at
※ Download: No matching unique or primary key for this column-list
You should use CONSTRAINT to set for composite foreign keys. The error has occurred because you are referencing a column in another table which is not unique. CREATE TABLE BOOK BOOKNO NUMBER 4 PRIMARY KEY NOT NULL, BTIME NUMBER 24,22 , CNAME VARCHAR2 10 , BTYPE VARCHAR2 13 , TABLENO NUMBER 2 , EMPNO NUMBER 4 , ENAME VARCHAR2 10 , BDATE DATE DEFAULT '23-MAR-13', FOREIGN KEY CNAME REFERENCES BOOK BOOKNO ; UPDATE CREATE TABLE BOOK BOOKNO NUMBER 4 PRIMARY KEY NOT NULL, BTIME NUMBER 24,22 , CNAME VARCHAR2 10 NOT NULL UNIQUE, BTYPE VARCHAR2 13 , TABLENO NUMBER 2 , EMPNO NUMBER 4 , ENAME VARCHAR2 10 , BDATE DATE DEFAULT '23-MAR-13', FOREIGN KEY CNAME REFERENCES BOOK CNAME ; The original post and the answer both have3 Book. Which one the child table's id will refer to?
Get answers and train to solve all your tech problems - anytime, anywhere. No a unique index is not necessarily created for a unique constraint. If one cannot be found then one is created at the time the constraint is created.
Join the world’s largest interactive community dedicated to Oracle technologies. - You have a bug in your code. I have read other posts but I still dont understand this error?
I am trying to establish a foreign key relation ship to a table as per the below script and it is throwing the below mentioned error.. Please let me know.. Thanks I wear a lot of hats... I wear a lot of hats - Developer, Database Administrator, Help Desk, etc. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform. The FK needs to reference either a PK or unique index on the parent table. Select all I always thought a unique index implied a unique constraint. I learned something new today... Just create the unique constraint manually and the FK constraint will work. The index does not need to be unique. Select all This is actually the preferred way to do it. The reason is that you can disable the unique constraint, do maintenance and not have to recreate the index in the process of enabling the constraint. Ideally the primary key should be created in the same fashion. No a unique index is not necessarily created for a unique constraint. When creating a primary key or unique constraint, the system tries to use an existing index to enforce that constraint. If one cannot be found then one is created at the time the constraint is created. This was done to prevent extra indexing that really isn't necessary. For example, you could have a normal index on col1, col2, col3 and then create a unique constraint on col1. The existing index will be used to enforce that constraint, even though there are more columns in the index. Again, the biggest advantage to this is that if you need to disable and enable the constraint, the index does not have to be rebuilt. I believe this feature was introduced in 9i. It's more than this solution. Get answers and train to solve all your tech problems - anytime, anywhere. Edge Out The Competition for your dream job with proven skills and certifications. Stand Out as the employee with proven skills. Move Your Career Forward with certification training in the latest technologies.
BUT this can lead to confusing results. Class : A references clause in a created or Alter table statement gives a column list for which there is no matching unique or primary key constraint in the reference table. Which one the child table's id will refer to. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc. The good answers are already given by Lennart and Balazs Papp. It's more than this solution. I have read other posts but I still dont understand this error. UserID should be a foreign key to User.
