CODEWITHSUNDEEP
oracle-databaseforeign-key-relationship
Abinash Koirala
Abinash Koirala

Reputation: 987

Can two or more tables which share same foreign keys share a constraint on that foreign key?

What is the problem with this code?? It gives error "name already used by another constraint". Also if I can't define same constraint in different tables then is there any way I can reuse the previously defined constraint?

Any insight??

CREATE TABLE tbl_formats
(
  format_id NUMBER(5),
  format_name VARCHAR2(50),
  format_desc VARCHAR2(100),
  valid_from DATE,
  valid_to DATE,
  format_type VARCHAR2(50),
  CONSTRAINT pk_format_id PRIMARY KEY(format_id)
);


CREATE TABLE tbl_format_detail
(
  id NUMBER(10),
  format_id NUMBER(5),
  src_field VARCHAR2(200),
  target_field VARCHAR2(100),
  business_rule VARCHAR2(4000),
  expression VARCHAR2(4000),
  target_segment VARCHAR2(4),

  CONSTRAINT pk_id PRIMARY KEY(id),
  CONSTRAINT fk_format_id FOREIGN KEY(format_id) REFERENCES tbl_formats(format_id)
);

CREATE TABLE tbl_client_formats 
(
  client_format_id NUMBER(10),
  format_id NUMBER(5),
  client_id NUMBER(5),
  CONSTRAINT pk_client_format_id PRIMARY KEY(client_format_id),
  CONSTRAINT fk_format_id FOREIGN KEY(format_id) REFERENCES tbl_formats(format_id),
  CONSTRAINT fk_client_id FOREIGN KEY(client_id) REFERENCES tbl_clients(client_id)
);

It seem like the foreign key constraint 'fk_format_id' defined in the table 'tbl_client_formats' conflicts with the same constraint already defined in the table 'tbl_format_detail'. I am new to oracle so explain even the obvious things please.

Upvotes: 3

Views: 5439

Answers (2)

goodfriend0
goodfriend0

Reputation: 303

Foreign key are stored in a database range, not a table range. You cannot have two FK with the same name on the same database, even if they are not in the same table. You could name your FK that way:

FK_PARENT_CHILD_FIELD

ex:

FK_FORMATDETAILS_FORMATS_ID,

FK_CLIENTFORMATS_FORMATS_ID,

FK_CLIENTFORMATS_CLIENT_ID

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30775

The problem is that you're trying to use the same constraint name twice. Just use a different name for your second constraint (e.g. fk_client_formats_format_id), and you should be fine.

Generally, I'd recommend using the table name as part of the constraint name, to avoid name clashes (if the constraint name gets too long, you'll have to use some kind of abbreviation scheme).

Upvotes: 5

Related Questions