multi primary key

hi
i have a table "trans"
year  number
month  number
tran_no  number

trans_no should not be reapet within same year
but it could be for different year
NiceMan331Asked:
Who is Participating?
 
Leon KammerConnect With a Mentor Commented:
To redefine your primary key to include more than one column, so the combination of values is unique for year and trans_no:

ALTER TABLE trans
DROP CONSTRAINT pk_trans;

ALTER TABLE trans
ADD CONSTRAINT pk_trans PRIMARY KEY (tran_no, year);

Just make sure there are no null values in the 2 columns, else thew constraint will fail to be created.

Cheers

Leon
0
 
NiceMan331Author Commented:
i found a constraint in the table
but i didn't find the related field
how to check it ?
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Did you not create the schema?

>>how to check it ?

What are you asking?

Are you asking what how that constraint is defined?

You 'could' query user_cons_columns to see the columns involved in the constraint but I would just generate the DDL:

select dbms_metadata.get_ddl('CONSTRAINT','CONS_NAME') from dual;

Just replace CONS_NAME with your constraint name.
0
 
NiceMan331Author Commented:
CORRECT
the constraint already defined 2 fields as PK
thanx to both of you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.