Having primary key in both fields but repeated in either fields allowed repeating

oracleoracle-11g

Is it possible to have both fields to be primary key but allows repeating For either 1 field in Oracle SQL.

   Student_ID  Date
   1001        23/10/2016  // allowed
   1002        23/10/2016  // allowed
   1002        24/10/2016  // allowed
   1002        24/10/2016  // not allowed

Best Answer

You can achieve the same goal by having a UNIQUE composite index on the desired fields.

ALTER TABLE YourTable
  add CONSTRAINT YourTable_unique UNIQUE (A, B,....);

(A, B,...) - fields A, B,... should be declared as NOT NULL.

This might be helpful if you have a surrogate PRIMARY KEY which you may want to keep. Something similar was recently asked for MySQL here, but the principle is the same.

You can also perform the same operation during the CREATE TABLE statement:

CREATE TABLE table_name
(
  column1 datatype NOT NULL,
  column2 datatype NOT NULL,
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);