ORACLENERD: Constraints: PK = UQ?

Constraints: PK = UQ?

Another little thing I learned today, you can use uniquely constrained columns in referential integrity. Looking it up in the docs, I found this little blurb:

Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.

Not really hard to miss I guess.

The only reason I found this was because I made the changes to the codes table, putting a UNIQUE constraint on the former PK, and nothing broke when I rebuilt the database.

So here are my 2 tables:

CREATE TABLE my_codes
(
  mycode VARCHAR2(20)
    CONSTRAINT pk_mycode PRIMARY KEY
);

CREATE TABLE t
(
  tid NUMBER(10)
    CONSTRAINT pk_tid PRIMARY KEY,
  mycode
    CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
    CONSTRAINT nn_mycode_t NOT NULL
);

I want to add a surrogate key to the MY_CODES table, per this discussion. I made a compromise, essentially anything that will or could be user entered will have a surrogate key (either SYS_GUID or sequence generated).

My first step was to remove the constraint on T, then drop the PK constraint on MY_CODES, add a new column that will hold the surrogate key and finally add the PK constraint to the new column.

ALTER TABLE t DROP CONSTRAINT fk_mycode_t;

ALTER TABLE my_codes DROP CONSTRAINT pk_mycode;

ALTER TABLE my_codes ADD ( mycodeid NUMBER(10) );

ALTER TABLE my_codes
  ADD CONSTRAINT pk_mycodeid
  PRIMARY KEY ( mycodeid );

Since I'm changing the meaning, I want to make sure that someone doesn't enter the same code twice, so I add a UNIQUE constraint on the table.

ALTER TABLE my_codes
  ADD CONSTRAINT uq_mycode
  UNIQUE ( mycode );

I rebuild my tables, without referencing the new PK.

CJUSTICE@TESTING>CREATE TABLE my_codes
  2  (
  3    mycodeid NUMBER(10)
  4      CONSTRAINT pk_mycodeid PRIMARY KEY,
  5    mycode VARCHAR2(20)
  6      CONSTRAINT uq_mycode UNIQUE
  7      CONSTRAINT nn_mycode NOT NULL
  8  );

Table created.

CJUSTICE@TESTING>CREATE TABLE t
  2  (
  3    tid NUMBER(10)
  4      CONSTRAINT pk_tid PRIMARY KEY,
  5    mycode
  6      CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
  7      CONSTRAINT nn_mycode_t NOT NULL
  8  );

Table created.