CODEWITHSUNDEEP
oracle-databasecomposite-primary-key
Guru
Guru

Reputation: 37

How to add composite primary keys?

I have a table with three columns, [Id,QTY,Date]. out of these three, two columns [id and date], should be set as primary keys, because I need to fetch the record one by one, from this table, into a reference.

the data to be inserted into this table is

101,10,NULL    
101,20,201220    
101,7,201440    
102,5,null    
102,8,201352

date is in yyyyww format

How do I define two columns as composite primary keys when they have null values, duplicates?

alter table abc add constraint pk primary key (ID, DATE);

if I try to alter the table the error appears

Error report:

SQL Error: ORA-01449: column contains NULL values; cannot alter to NOT NULL
01449. 00000 -  "column contains NULL values; cannot alter to NOT NULL"
*Cause:    
*Action:

Upvotes: 1

Views: 18960

Answers (2)

DB_learner
DB_learner

Reputation: 1026

The column name of your table is ID and it is still null and non-unique, how is it possible. If it is primary key of other table try adding a surrogate key column for this table and make it primary key.

In case of composite primary key, it should have atleast one not null value(For each row) in the combination of columns. And the combination of column must be unique at all case.

For further details check, http://docs.oracle.com/cd/B10500_01/server.920/a96524/c22integ.htm

Correction - If composite primary key is made up of 3 columns, then no column (among 3) can hold NULL value. And the combination of those 3 columns must be unique. E.g. (1,2,2) (1,2,1) (2,2,1) (1,2,2) - not valid

Upvotes: 0

DevYudh
DevYudh

Reputation: 2737

Using table level constraint, you can use this query

alter table your_table add constraint pkc_Name primary key (column1, column2)

but first you need to declare the columns NOT NULL. All parts of a primary key need to be NOT NULL.

Upvotes: 3

Related Questions