CODEWITHSUNDEEP
sqloracle-databasedatabase-design
Yamenovic
Yamenovic

Reputation: 1

how can i create constraint on table based on many column and value in another table Oracle

i have atable such as codes table have these values:

Major_code minor_code name
1 0 Country
1 1 USA
1 2 JOR
2 0 Food
2 1 Burger
2 2 Mansaf

I need to create table have these columns (ID, Country_ID , Food_ID) what is the best constraint that resolve this issue?

Upvotes: 0

Views: 354

Answers (3)

APC
APC

Reputation: 146199

Having a single lookup table for all reference codes is a very poor design, to the point of almost being a SQL anti-pattern. It is likely to have performance issues. It is also difficult to ensure that the value of COUNTRY_MINOR_CODE references an actual country minor code in the reference table. Which brings us to this statement:

I need to create table have these columns (ID, Country_ID , Food_ID)

Can't be done. Or at least it can be done but you can't build foreign keys on that, because minor keys are not unique in the referenced table. But if you are set on a single table, say lookup_table with primary key defined on (major_code, minor code), you need this:

create table country_food (
 id                 number primary_key
,country_major_code number not null
,country_minor_code number not null
,food_major_code    number not null
,food_minor_code    number not null
,constraint country_major_code_ck check (country_major_code = 1)
,constraint country_minor_code_ck check (country_minor_code != 0)
,constraint food_major_code_ck    check (country_major_code = 2)
,constraint food_minor_code_ck    check (country_minor_code != 0)
,constraint country_code_fk foreign key (country_major_code, country_minor_code) references lookup_table
,constraint food_code_fk foreign key (food_major_code, food_minor_code) references lookup_table
)
/

The hard coding of values, the additional check constraints, the increased complexity of joining to look up the name ... These are all reasons why you should choose to have separate tables for FOOD, COUNTRY, etc.

create table country (
 country_id    number       primary_key
,country_name  varchar2(24) not null 
)
/
create table food (
 food_id    number primary_key
,food_name  varchar2(24) not null 
)
/
create table country_food (
 id                 number primary_key
,country_id         number not null
,food_id            number not null
,food_major_code    number not null
,constraint country_code_fk foreign key (country_id) references country
,constraint food_code_fk foreign key (food_id) references food
)
/

Upvotes: 1

persian-theme
persian-theme

Reputation: 6638

First you need to consider the design of the tables

  1. Table of country
  2. Table of foods
  3. Tables of food_country -------- relationship => many-to-many
CREATE TABLE Country
( 
  Major_code numeric(15) not null,
  minor_code numeric(15),
  name varchar(50),
  CONSTRAINT country_pk PRIMARY KEY (Major_code)
);

CREATE TABLE Food
( 
  Food_ID numeric(15) not null,
  //...........................
  name varchar(50),
  CONSTRAINT food_pk PRIMARY KEY (Food_ID)
);

CREATE TABLE Counry_Food
( 
  ID numeric(10) not null,
  Country_ID numeric(15) not null,
  Food_ID numeric(15) not null,
  CONSTRAINT fk_country
    FOREIGN KEY (Country_ID)
    REFERENCES Country(Major_code),
  CONSTRAINT fk_food
    FOREIGN KEY (Food_ID)
    REFERENCES supplier(Food_ID),
);

Upvotes: 0

Serg
Serg

Reputation: 22811

If I've got it right, you can get all Country+Food pairs with the query

select t1.minor_code counrty_id, t2.minor_code food_id
from (
    select minor_code
    from  codesTable
    where Major_code = (
        select c.Major_code 
        from  codesTable c
        where c.minor_code=0 and c.name='Country') 
) t1
cross join (
    select minor_code
    from  codesTable
    where Major_code = (
        select c.Major_code 
        from  codesTable c
        where c.minor_code=0 and c.name='Food') 
) t2

You can use the query to insert data into a table with an autogenerated ID or use it any other way.

Upvotes: 0

Related Questions