PL/SQL :: Composite Primary Key?

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


ADVERTISEMENT

SQL & PL/SQL :: Querying From A Primary Composite Key

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

SQL & PL/SQL :: Normalization - Concept Of Primary Key / Composite Key

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

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 View Related

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?

View 10 Replies View Related

SQL & PL/SQL :: Example Of Composite And Candidate Key

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

SQL & PL/SQL :: Getting Minimum Composite Key From Group By

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

Composite Index - Performance?

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

SQL & PL/SQL :: ORA-06530 / Reference To Uninitialized Composite

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

SQL & PL/SQL :: Updating Sequence With Order Of Old Composite PK

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

Server Administration :: How To Apply Composite Key On A Table

Jul 21, 2011

How can i apply composite key on a table?

View 2 Replies View Related

SQL & PL/SQL :: Creating Composite Unique Key With Only 1 Column Allowed To Be Changed

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

SQL & PL/SQL :: Composite Index Having Varchar2 Number And CLOB Column

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

Performance Tuning :: Creating Normal / Composite Indexes On Fields

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

Performance Tuning :: Composite Index - Explain Plan Full Table Scan On Lookup_fein?

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

During Primary (old Standby) To Standby (old Primary) Switchover Hang?

Apr 4, 2013

I am using RAC to RAC data guard environment. Os hp-unix. during primary(old standby) to standby(old primary) switchover I am ussing:

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

I have 11.2.0.1 environment with standby and when performing switch over - it was hanging for more than 2 hours and i had to cancel it. This is what all i see in the database alert.log:

Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m000_15160.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Thu Apr 04 17:44:03 2013
Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m000_15780.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Thu Apr 04 17:59:03 2013
Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m001_16373.trc:

[code]....

ORA-01155: the database is being opened, closed, mounted or dismounted.

View 1 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:

Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....

I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

View 5 Replies View Related

SQL & PL/SQL :: Primary Key On A View

Dec 20, 2011

While making a view.

1. Is it advisable to have primary key in a view.

2. Why can't we have a primary key in a view, what are its advantages. I mean one cannot search a view by index.

3. Is it because of hibernate, but again i have used distinct keyword in a view declaration which means i cannot insert via view.

Here is my code for view to make a primary key in a view.

CREATE OR REPLACE FORCE VIEW VU_NAME
PRIMARY_KEY, NAME_ID, ADDRESS_ID
)

[Code]....

alter view VU_NAME add constraint vemp_pk primary key (PRIMARY_KEY) disable

View 3 Replies View Related

PL/SQL :: Primary Key Value Updation

Jul 13, 2012

I have a table called Order_Mstr

order_id order_Desc
-------- ----------
1 Order1
2 Order2
3 Order3
4 Order4

Child tables referring Order_id are:

Order_child1
Order_child2
Order_child3
Order_child4
Order_child5
Order_child6
.
.
.
Order_child50

I have to update Order_id with some 8 digit number generated by some algorithm. And i have to update that value in child tables as well. In how many ways i can do this task. for example..

1) Disabling the constraint and updating Master value and child value.

View 5 Replies View Related

PL/SQL :: How To Create Primary Key Like W1

Jul 10, 2012

primary key should be

W1
W2
W3 like this.

1,2,3 are sequence numbers.

View 2 Replies View Related

PL/SQL :: Primary Vs Surrogate Key

Apr 17, 2013

what is the difference between these two and in where we use Primary key and where we choose Surrogate Key.How to understand the difference between these two keys.

View 7 Replies View Related

Finding Primary Key In A Table?

Oct 4, 2007

Whats the command to show the name of the primary key in a table using oracle sql plus?

View 1 Replies View Related

SQL & PL/SQL :: Auto Number For Primary Key

Feb 19, 2011

I have set up a schema using a DDL script in Oracle 10g. I am linking this through to a Visual Basic 2008 fron end. The connection is fine. My database of is for a car hire company project.

I need to know how to create an auto increment for a primary key field, like Access does.

For example here is my table structure for my Rental_Payment table.

-- TABLE RENTAL_PAYMENT
CREATE TABLE RENTAL_PAYMENT (
PAYMENTNO NUMBER NOT NULL,
CARDNO NUMBER NOT NULL,

[Code]....

So for example the first record would appear as:

PAYMENTNO CARDNO AMOUNT CARD_TYPE
======== ===== ====== =======
01

I would then enter values and the next payment number would be 02 etc.

PAYMENTNO CARDNO AMOUNT CARD_TYPE
======== ===== ====== =======
01 123412 £40.00 VISA
02

Visual Basic will usually have a star (*) beside a new entry field so 02 will be
* 02

etc........

I have about creating a sequence. So how can I tie this to my table?

View 12 Replies View Related

SQL & PL/SQL :: Foreign Key - Primary Key Relation

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

SQL & PL/SQL :: Primary Key Violation Error

Jun 25, 2013

In my application I am facing peculiar error. For easy understanding I am considering emp table. In my package P1 I have two procedures Proc1,Proc2.

Proc1 : It is receiving employee details and for checking if employee already exists it is calling Proc2.
Proc2 : Here it checks if employee exists. If exists it deletes old record, commit and inserts new record in sequence.

But however in few instances Proc2 is returning unique constraint (CONSTRAINT_NAME) violated. I am very much confused why this error is coming.

View 12 Replies View Related

SQL & PL/SQL :: Index Related To Primary Key

Jul 12, 2012

What is the default key created when a primary key is created in a table ?

View 1 Replies View Related

SQL & PL/SQL :: Table Partitioning With No Primary Key

Jan 24, 2013

we apply partitioning concept on a table which don't have any primary key ?

I just want to add one more field as primary key with some sequence generated values while partitioning ? Is it possible ?

View 7 Replies View Related

Windows :: Set One Oracle As Primary Out Of 2?

Jul 13, 2010

On my system 2 oracles are installed for different applications one having version 8.0 & other 10.0.

The oracle 10.0 is required for Windchill application. When I am trying to access the sqlplus by windchill shell it access the Oracle 8.0 sqlplus. how to set one oracle as primary on system so i am able to access the oracle 10 sqlplus in windchill shell.

View 4 Replies View Related

SQL & PL/SQL :: Updating A Duplicate Primary Key

Oct 8, 2012

PM_KEY.. PCN....... JOBNO...PM_VERNO
20......... 137....... XX23..... 0
21......... 137....... XX24..... 1
22......... 137....... XX17..... 2
23......... 137....... XX81..... 3
22......... 137....... XX90..... 2

I have a dilemma......the constraint was disable somehow in my table of about 900,000 records which allowed the insertion on duplicate primary keys as well as duplicate records. I've managed to get rid of the duplicate records, but I haven't quite figured out how to update the primary key and version number. I've tried the following but

UPDATE TABLE
SET PM_PM_KEY=(MAX(PM_KEY)+1), PM_VERNO=(MAX(PM_VERNO)+1)
WHERE TBLDATE= MAX(TBLDATE)
ORDER BY TBLDATE ASC
GROUP BY PCN;

The query is failing with
Error at Command Line:2 Column:19

Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Primary And Natural Key

Jul 5, 2011

what is the difference between primary key and natural key ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved