SQL & PL/SQL :: Foreign Key References Composite Primary Key
Jul 4, 2013
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)
);
Create Table Qualified
(
FacultyID number (5,0) NOT NULL,
CourseID varchar2 (10) NOT NULL,
Date_Qualified varchar2 (10) NOT NULL,
Constraint Qualified_PK Primary Key (FacultyID, CourseID),
Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
);
Create Table Section
(
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
CourseID varchar2 (12) NOT NULL,
Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
);
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)
);
This is the error I am receiving:
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.
View 5 Replies
ADVERTISEMENT
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?
View 10 Replies
View Related
Aug 25, 2010
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.
Create Table Car_Mapping_Details
(Car_Cd VARCHAR2(5),
Manufacturer_Cd VARCHAR2(5));
--Primary Key Constraint
ALTER TABLE Car_Mapping_Details
ADD CONSTRAINT Car_Mapping_Details_pk PRIMARY KEY (Car_Cd );
--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?
View 2 Replies
View Related
Jun 26, 2012
understanding composite primary key.
I know what is composite key and how it stores the data.. but i am not able to find when i have to choose the columns to be composite keys.
View 10 Replies
View Related
May 20, 2010
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.
View 7 Replies
View Related
May 17, 2013
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?
View 12 Replies
View Related
May 2, 2012
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
ALTER TABLE Table_1
ADD CONSTRAINT FK_VACC_ID
FOREIGN KEY (VACC_ID)
REFERENCES Table_2(VACC_ID)
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.
View 4 Replies
View Related
Sep 29, 2010
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?
View 11 Replies
View Related
Nov 16, 2012
I have a requirement like below
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.
View 3 Replies
View Related
Feb 17, 2013
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies
View Related
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...
View 1 Replies
View Related
Mar 11, 2011
I have created table as below
create table emp_temp as select * from emp;
the table is created, but the constraints are not copied. Is there any way to copy all the constraints.
View 3 Replies
View Related
Oct 8, 2010
is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies
View Related
Sep 14, 2011
I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).
I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.
How can I find these references?
View 4 Replies
View Related
Jan 23, 2012
i was reading oracle report 10g , i tried to do lexical references in Oracle® Reports
Building Reports
10g (9.0.4)
Part No. B10602-01
i don't understand parent query and child query relationship
View 3 Replies
View Related
Jul 25, 2013
concept of composite and candidate keys. i have gone through all the definition part but did not understand the concept of creating candidate key for example:-
composite key
create table test(id number,pincode number(4),name varchar2(15),primary key(id,pincode));
table created with composite bcz i took pair of id and pincode.
here base on the same example want to create candidate key with the syntax.
View 24 Replies
View Related
Oct 11, 2011
I have the following case:
create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)
[Code]....
I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used
select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;
I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.
View 4 Replies
View Related
Nov 8, 2012
I have a lot of queries on a table with WHERE clause:
WHERE CR_DATE > :y AND CR_VALUE = :x
Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?
View 1 Replies
View Related
Sep 24, 2013
problem with populating the collection object.
I am getting an error -
'ORA-06530: Reference to uninitialized composite"
My Code is as below
create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is
[code]....
View 13 Replies
View Related
Sep 5, 2011
I am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.
CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....
I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..
View 33 Replies
View Related
Jul 21, 2011
How can i apply composite key on a table?
View 2 Replies
View Related
Jul 3, 2013
I have a table say MY_TAB with columns as below
emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
View 13 Replies
View Related
Feb 25, 2012
I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...
[URL]......
Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?
View 11 Replies
View Related
Feb 14, 2013
I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?
11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;
Table dropped.
Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....
- dynamic sampling used for this statement
22 rows selected.
View 8 Replies
View Related
May 12, 2011
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.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
View 1 Replies
View Related
Nov 7, 2011
if have table primary key in this table empno and another table foreign key is empno and when add in the primary key 5522 how can
5522 in the empno "foreign key" automatic
View 5 Replies
View Related
Jul 26, 2011
I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:
following is my trial:
create table abc
(
pk_id number(4) primary key,
abc_name varchar2(10),
fk_id number(4)
);
create table xyz
(
x_id1 number(4),
x_id2 number(4),
x_name varchar2(10)
);
commit;
insert all
into abc values (10,'ab1',1)
into abc values (11,'ab2',2)
into abc values (12,'ab3',5)
into abc values (13,'ab4',7)
into abc values (14,'ab5',9)
into xyz values (1,1,'d1')
into xyz values (2,2,'d2')
into xyz values (5,3,'d3')
into xyz values (7,4,'d4')
into xyz values (9,5,'d5')
select * from dual;
commit;
--this following select returns 5 rows
select * from abc, xyz where abc.fk_id = xyz.x_id1;
-- the following update only updates 3 rows and sets the other 2
-- to null!
update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id);
commit;
select * from abc;
View 3 Replies
View Related
Dec 4, 2011
I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my
CREATE TABLE Items
(ItemID NUMBER(5) NOT NULL,
Description VARCHAR2(30),
ListPrice NUMBER(9,2) NOT NULL,
QtyOnHand Number(5),
ReorderPoint NUMBER(3),
ReorderQty NUMBER(3),
OnBackOrder Number(3),
CONSTRAINT ItemsPK
[Code] ............
There's three sets of inserts the first two successfully inserts the data NO problem but when i try to insert the data on SupplierItems comes as:
ERROR at line 1:
ORA-02291: integrity constraint (jcarter.SUPPLIERITEMSFK2) violated - parent key not found
I looked on the forum and it appears this happens when someone is doing a Insert ALL but this is not my case..
View 2 Replies
View Related
Feb 3, 2012
I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,
but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.
View 4 Replies
View Related
Mar 13, 2013
You should almost always index foreign keys because they are frequently used in joins. In addition, if you intend to delete or update unique or primary keys on the parent table, you should index the foreign keys to improve the locking of child records.what I don't understand are, I should create index on foreign key column in parent table or child table or both ?
View 1 Replies
View Related
