In my database (MS SQL Server) I use language specific texts. So I defined table Texts as follows:
CREATE TABLE [dbo].[Texts](
[Id] [int] NOT NULL,
[language] [nchar](5) NOT NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_Texts] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[language] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Texts] WITH CHECK ADD CONSTRAINT [CK_Texts_language]
CHECK (([language]='en-US' OR [language]='de-DE'))
ALTER TABLE [dbo].[Texts] CHECK CONSTRAINT [CK_Texts_language]
These texts should be used anywhere in my tables, e.g. here:
CREATE TABLE [dbo].[MyObject](
[id] [int] NOT NULL,
[name] [int] NOT NULL,
[comment] [int] NOT NULL,
CONSTRAINT [PK_PlantDescription] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
How can I specify the constraint that a value of MyObject.name
or MyObject.comment
should be a value of the partial key Texts.Id
?
There may be several texts for one id, with different languages. So (Id, language
) is unique, not Id
itself.
EDIT:
Here is an example for table Texts:
Id language text
1 de-DE Holunder
1 en-US elder
2 de-DE Von diedem Busch kann man sowohl die Blüten als auch die Früchte verwerten.
2 en-US From this bush you can use both flowers and fruits.
and table MyObject:
Id name comment
512 1 2
Together with UDF
CREATE FUNCTION [dbo].[GetText]
(
@id int,
@language nchar(5)
)
RETURNS nvarchar(MAX)
AS
BEGIN
Declare @result nvarchar(max);
Select @result = text from dbo.Texts where id = @id AND [language] = @language;
if @result is NULL
BEGIN
Select TOP 1 @result = text from dbo.Texts where id = @id ;
END
return @result;
END
With these definitions and the following query
SELECT [Id]
,[dbo].[GetText]([name], 'en-US') AS Name
,[dbo].[GetText]([comment], 'en-US') AS Comment
FROM [dbo].[PlantDescription]
I get the result:
Id Name Comment
512 elder From this bush you can use both flowers and fruits.
You see: When I delete row (Id = 1), there will be orphaned rows that I have to delete by a trigger.
Or is there a standard way for language specific texts that I didn't find in the inetrnet?
Best Answer
One way is to create a domain table for the text keys:
Now you can use
TEXT_KEY
fromTEXT_KEYS
as a foreign key for your other tables.