SQL & PL/SQL :: Add Composite Primary Key To Table That Has Self Referencing Foreign Key
Jan 21, 2012
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 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.
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?
I have the following 3 columns in my VERSIONS table.
MAJOR MINOR MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions select max(minor) as max_minor from versions where major = :max_major select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
I am following Lynda Tutorial for Normalization. I have understand normalization.The core concept of primary key is that it uniquely identifies each record in the table, but here in the given below image the 'COURSE' field is repeating 'SQL101' value again and again but still the teacher in the video is calling it primary key and combination of date & Course column Composite key.how can a field which vales are repeating can be called as Primary Key?
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 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.
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...
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup 1A A1 2A A1 2B A1 1B A1 4A A1 5B A1 5A A1 6C B1 -- new group as the emp and dept are not mapped previously 7A A1 8D C1
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.
I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1; create table t1 (slno number, acc_no number);
drop table t2; create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3; create table t3 (acc_no1 number primary key, acc_name1 varchar2(100)); [code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
I have now created my tables and now I want to populate them with my data but I get errors below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding"
i have a field in my table office i got field office_code ,this field is been used in diffirent tables as foreign key is there a sql i can wirte to see all tables who have used this field as foreign key.
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'm having a bit of a problem getting the syntax of a trigger right. Unfortunately, I have no DBAs locally, I use some third party software, and for reasons beyond my understanding, I have been told to use triggers, and not stored procedures, so I'm running with it.
The set up:
STRUCTURES table: contains several columns, one of which is the unique ID column. ASSAY table: contains several rows, also with the same ID column, but can have more than one row per ID (several assays per compound). One column is XC_ASSAYS.
The idea of the trigger is basically: When a row in the ASSAYS table is updated, pull out the ID of the row, then calculate the average of the XC_ASSAYS columns for those rows, and report it to the STRUCTURES.XC_ASSAY column for that row ID.
My best attempt thus far results in compilation errors.
CREATE TRIGGER INHIB_W_ALA_TR AFTER INSERT OR UPDATE ON ASSAYS FOR EACH ROW BEGIN UPDATE STRUCTURES SET XC_ASSAY = (SELECT AVG(XC_ASSAY) FROM ASSAYS WHERE ASSAYS.ID = :NEW.ID) WHERE STRUCTURES.ID = :NEW.ID END; /
The resulting errors are: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/1 PL/SQL: SQL Statement ignored 2/190 PL/SQL: ORA-00933: SQL command not properly ended 12/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
[code]...
I don't understand some of the errors, such as why line 2 SQL is ignored (it seems correct?), or I'm supposed to properly terminate the trigger (I've read ; and /, but I'm getting the end-of-file errors when I do so). Tried shuffling syntax and ' or " around - and I can't get it.The body SQL works when I replace :NEW.ID with an actual variable (such as 'NMP12'), but I'm not sure how to pass the ID variable from the updated row into the body. The ID is not updated, but other columns are.
I have a table called dept table with nested collection as below. Nested Table Collection:
create or replace type courselist as table of varchar2(64) Table Using Nested Table Collection: CREATE TABLE "FCSDWH_STG"."DEPT" ( "NAME" VARCHAR2(20 BYTE), "DIRECTOR" VARCHAR2(20 BYTE), "OFFICE" VARCHAR2(20 BYTE), "COURSES" "FCSDWH_STG"."COURSELIST" ) Content Of Table:NameDirectorofficecoursesAccountingJames CharlesUNOFCSDWH_STG.COURSELIST('natural science','chemistry','Computer Science','Computer Science')
I am trying to select and print an element from nested table collection using below plsql block.
MY_COURSE VARCHAR2(64 CHAR); BEGIN SELECT courses INTO MY_COURSE FROM TABLE(SELECT COURSES FROM DEPT) where courses='chemistry'; DBMS_OUTPUT.PUT_LINE(MY_COURSE); END; Error Message: PL/SQL: ORA-00904: "COURSES": invalid identifier
How can I reference an Object Type created on a remote database?This is the escenario:
In DATABASE A: CREATE OR REPLACETYPE USERA.EXPO_EXPEDIENTES_RECAUDOS OID 'DCADCB2EA2344DFAB1D205C03D708359' AS OBJECT ( exer_cd_expediente VARCHAR2 (50), exer_id_ident_expediente VARCHAR2 (30), exer_cd_sucursal NUMBER (3), exer_cd_ramo NUMBER (2), exer_nu_poliza NUMBER (7), exer_nu_certificado NUMBER (9), exer_nu_contrato NUMBER (7), exer_cd_nacionalidad VARCHAR2 (1), exer_nu_cedula_rif NUMBER (9), exer_nm_titular VARCHAR2 (70), exer_st_expediente VARCHAR2 (2), exer_de_status_exp VARCHAR2 (240), exer_fe_status_exp DATE, exer_cd_productor NUMBER (5), exer_nm_productor VARCHAR2 (60), exer_cd_mail_productor VARCHAR2 (50), exer_in_habilitado VARCHAR2 (1), exer_in_permite_habilitar VARCHAR2 (1), exer_in_carga_consulta VARCHAR2 (1), exer_cd_ramo_aplicacion VARCHAR2 (200), exer_cd_producto VARCHAR2 (6) )/ In DATABASE B:
After creating the public synonym and asigning the required privileges on the object in DATABASE A, I try to execute the following:
DECLARE x EXPO_ EXPEDIENTES_RECAUDOS;BEGIN null;END;
But i got the following error:
ORA-06550: line 2, column 12:PLS-00331: illegal reference to
USERA. EXPC_ EXPEDIENTES_RECAUDOS@DATABASEA
After investigating a little i found the following, but i dont know how to apply it, "The CREATE TYPE statement has an optional keyword OID, which associates a user-specified object identifier (OID) with the type definition. It should be used by anyone who creates an object type that will be used in more than one database."
(I put ... because there are a lot of columns which are not interesting for this question.)
Remark: team_id and agent_id are neither primary keys nor UNIQUE.
In a third table I have a column with exactly the same data type, that is, VARCHAR2(20). What I need is to make sure that its value is at least in one of the tables TEAM or AGENT (so the only thing that is important is to check that the specified value exists no matter in which of the two tables, but just to make sure that it exists somewhere).
The solution that I have found so far, is to create a BEFORE INSERT trigger on the table. Within the trigger I create a cursor
CURSOR c IS SELECT team_id FROM TEAM UNION SELECT agent_id FROM AGENT;
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
