Creating List Of Items And Using Foreign Keys As Primary
May 19, 2007
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
Create table Car (Car_cd VARCHAR2(5), Car_Desc VARCHAR2(50) ); alter table Car add constraint Car_PK primary key (Car_CD); INSERT INTO Car (Car_Cd, Car_Desc) VALUES ('CORLA','COROLLA'); Commit; [code]....
The requirement necessitates a new table to map car to manufacturer. This mapping table may later be expanded to contain other attributes Engine, MPG, etc to hold details specific to a car.But this is in future.
--Not able to create this as Car_cd is already a PK in this table and therefore has Unique Index ALTER TABLE Car_Mapping_Details ADD CONSTRAINT Car_Mapping_Details_fk1 FOREIGN KEY (Car_Cd)REFERENCES Car (Car_Cd); [code]....
But in this case the Car_Mapping_Details.Car_cd is itself is a primary key and therefore has Unique index.Although I was able to create foreign key constraint on Car_mapping_details.car_cd column (which is also Primary Key), I was not able to create Foreign Key Index on this column. It gives me Quote:ORA-01408: such column list already indexed.In other words, not creating foreign index for foreign key column lead to table-level lock? Or will the Unique Index in that primary key column be sufficient to avoid table-level lock?
In my sql developer I have created a table. Created primary keys. Using alter table I have also created the foreign key. Now I need to add one more foreign key to the same table. Do I have to drop the existing foreign key and then create the 2 new foreign keys using the alter table condition or can I retain the existing FK and just use alter table to add another FK.
I have few BDs replicated using Advanced Replication, some tables are read only (Basic Replication) and another ones are Updatable (Advanced Replication).
The infraestucture is Materialized View Replication, my trouble is, I do not know how to should treat the updatable tables that have foreign keys, I read in another FAQs that you should replicate the indexes using the same way to replicate tables (dmbs_repcat.create_master_repobject)
Which is the correct manner to treat the foreign keys with this kind of updatable snapshots,
I have to create indexes on foreign key columns ,now if composite index is already there with foreign key column then that will work or i will have to create a single column index.
I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.
DROP TABLE emp; CREATE TABLE emp AS (SELECT * FROM scott.emp); CREATE TABLE audit_table
Inside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.
In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.
I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.
how will i take backup including with primary keys?
I am trying to establish PK-FK relationship between 2 tables. Table 1 has VACC_ID as a primary key. Table 2 has VACC_ID and SCH_ID as the primary keys. I am trying to add VACC_ID from Table 2 as a FK to Table 1. This is the code I used
I also tried to add it without writing the query and editing the Table 1. By default, I am getting 2 columns(both VACC_ID) in the local column being referenced to the 2 primary keys(VACC_ID and SCH_ID) of table 2. I also uploaded the screeshot of the error, when I run the query.
I am working on a new project in OBIEE. I am asked to do the data modeling in the database using oracle sql developer. I have to create the joins based on the requirements. I have the tables created already. But the primary keys for few tables are not defined for few tables. PK-FK joins are also not done properly.
My questions are (1) If I have to define the primary key for the existing tables can I do that using the alter table command or should I create the table all over again and then define it? (2) If I have to make the changes in the existing PK-FK joins how do i go about doing that?
I have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.
Create Table Student ( StudentID number (6,0) NOT NULL, Student_Name varchar2 (20) NOT NULL, Constraint Student_PK Primary Key (StudentID) );
Create Table Faculty ( FacultyID number (5,0) NOT NULL, Faculty_Name varchar2 (20) NOT NULL, Constraint Faculty_PK Primary Key (FacultyID) );
Create Table Course ( CourseID varchar2 (10) NOT NULL, Course_Name varchar2 (20) NOT NULL, Constraint Course_PK Primary Key (CourseID) );
Error starting at line 1 in command: Create Table Registration ( StudentID number (6,0) NOT NULL, Section_No number (5,0) NOT NULL, Semester varchar2 (7) NOT NULL, Constraint Registration_PK Primary Key (StudentID, Section_No, Semester), Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID), Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID), Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID) )
Error at Command Line:8 Column:104 Error report: SQL Error: ORA-02256: number of referencing columns must match referenced columns 02256. 00000 - "number of referencing columns must match referenced columns" *Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list. *Action: Make sure that the referencing columns match the referenced columns.
I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .
In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table
in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.
Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.
I have an employee table which has a primary key and a self referencing foreign key, as shown here
create table employee ( id not null, name not null, department not null, supervisor_id not null ,constraint constraint_1 primary key (id) ,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Now if i make the primary key composite, as shown below -
create table employee ( id not null, name not null, department not null, supervisor_id not null ,constraint constraint_1 primary key (id, name) ,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Oracle is throwing the following error -
ORA-02270: no matching unique or primary key for this column-list
How can this error be fixed without changing the composite primary key?
I have build a form based on a table with 2 columns,one of which isprimary key. I have created a Button with name "Quit".For that i used when-button-pressed trigger with built in "exit_form". But when I run the form,with out any execution of query or any action if I press "Quit" button,it is not working
The error message is "FRM-40202 : Field must be entered" Then I used a trigger to the item with primary key.when-new-item-instance:
While using this,the error is "unable to resolve reference to item customer.custid" Cutomer is block name & custid is textitem with primary key. canvas used is Tab canvas with 3 tab pages with master detail relation b/w thwm. The primary key is required compulsory at the time on inserting.
Am creating a table based on some integrity constraints, but it's not working.
CREATE TABLE member ( member_id NUMBER(10), last_name VARCHAR2(25) NOT NULL, first_name VARCHAR2(25),
[code],,,
Error:
Error report: SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table. *Action: Find the correct column names using the ALL_CONS_COLUMNScatalog view
i want different options to appear in the list item when choosing different radio buttons.
i've tried to compile the codes and there are no errors.
I dunno what went wrong.
B_DEV IS THE BLOCKNAME LIST_ITEM IS THE NAME OF THE LIST ITEM SEVEN_ELEVEN, MANNINGS, GNC ARE THE RADIO BUTTONS NAMES FOR_BANNERS IS THE RADIO GROUP NAME
I want to create two list items. one is for deptno and another is for empno in a single form.
Based on the selected deptno value from first list item i want to populate corresponding empno from second list item. I tried with the fallowing code, it's work fine but the second list is not clears with the existing empno's.
Code for Deptno List item
declare cursor c is select deptno from dept; cnt number := 1; begin clear_list('LI_DEPTNO');
[Code]...
Code for Empno list item in when_list_changed trigger for deptno list item
declare cursor c is select empno from emp where deptno = :LI_DEPTNO; cnt number := 1; begin it_id := Find_Item('LI_EMPNO'); clear_list(it_id); for i in c loop add_list_element('LI_EMPNO',cnt,i.empno,i.empno); cnt := cnt + 1; end loop; end;
My form has two list boxes and two buttons add and remove. As and when i click add button, the selected value from left hand side list item should get populated to right hand side list item. And When I click Remove button, it should do vice versa.
I have a requirement to select multiple values from a list item and do some manipulations with those values. I tried by integrating the LOV with a text item, but couldn't able to select multiple items. Is there a way to do multiple selection in a list item ?
I'm trying to make a foreign key against a table which has a unique index instead of a primary key.
i get this error:
Error SQL: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:
or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)
I was cloning a schema user1 as user2 in the same database.
user1 had quota on 2 tablespaces user1_data and user1_index.
I created user with name as user2.
I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows
during import i encountered following errors for so many constraints
"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE" IMP-00003: ORACLE error 2270 encountered ORA-02270: no matching unique or primary key for this column-list IMP-00017: following statement failed with ORACLE error 2270:
I found that the it happened as the primary key of table1 was not created for which error was logged in the log file
. . importing table "table1" 19441 rows imported IMP-00015: following statement failed because the object already exists: "ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_" "ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F" "REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE " . . importing table "table5" 0 rows imported
However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?
I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.
I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.
There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.
