Database for multilanguage design. double unique key referece to foreign key
I have to make my database able to translate some terms. here is basic draw of my database:
first table(and many more like it):
create table objects
id int PRIMARY KEY,
name varchar(50),
name_id int FOREIGN KEY,
Second table:
create table language
language varchar(20)
language_id PRIMARY KEY
Third table:
create table translations
language_id FOREIGN KEY,
name_id UNIQUE KEY,
translation varchar(50)
it looks like this:
OBJECTS:
id | name | name_id
___|______|________
1 |apple |1
LANGUAGE:
language_id | laguage
____________|________
1 | ENG
____________|________
2 | DEU
TRANSLATIONS:
language_id | name_id | translation
____________|_________|____________
1 | 1 | apple
____________|_________|____________
2 | 1 | apfel
Now, both columns in translations, language_id and name_id have to unique. but only one, name_id have to reference the foreign key in table objects.
is it possible to do? if not, whats the alternative?
这是可能的，因为您可以在大多数数据库中创建UNIQUE约束：
-- Add unique constrain on two columns
ALTER TABLE TRANSLATIONS
ADD CONSTRAINT unq_translations UNIQUE (language_id, name_id);
-- Add foreign key constrain.
ALTER TABLE TRANSLATIONS
ADD CONSTRAINT fk_translations_name FOREIGN KEY (language_id, name_id) REFERENCES OBJECTS(language_id, id);
Use following alternative... Execute In order.
1. create table object(object_id number(10) primary key, object_name varchar(50));
2. create table language(language_id number(10) primary key, language_name varchar(20));
3. create table translation(translation_id number(10) primary key, translations varchar(50), object_id number(10), language_id number(10), foreign key(object_id) references object(object_id), foreign key(language_id) references language(language_id));
4. alter table translation add unique(object_id, language_id);
First, In the TRANSLATIONS table "name_id" is UNIQUE, i'm surprised how did you inserted duplicate entry in this column. Provide correct data for third table.
In TRANSLATIONS table name_id shouldn't contain duplicate values. Here only one column(with constraint) in TRANSLATIONS table that could contain duplicate entry is LANGUAGE_ID, and no benefit by making it unique as it is already unique in its parent table LANGUAGE.
However if you make column LANGUAGE_ID unique here, it will allow only those numbers of entries, that are in LANGUAGE table ie if LANGUAGE table contains 2 entries, you will be allowed to insert only 2 rows here in TRANSLATIONS table, AS I already said no benefit by doing this.
上一篇: 实体框架：创建并插入多对多关系