1

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?

3

One way is to create a domain table for the text keys:

CREATE TABLE TEXT_KEYS
( TEXT_KEY INT NOT NULL PRIMARY KEY ); 

CREATE TABLE Texts
( TEXT_KEY INT NOT NULL REFERENCES TEXT_KEYS (TEXT_KEY)
, [language] [nchar](5) NOT NULL
, [text] [nvarchar](max) NULL
,   CONSTRAINT [PK_Texts] PRIMARY KEY CLUSTERED 
    ( TEXT_KEY ASC, [language] ASC
[...]

Now you can use TEXT_KEY from TEXT_KEYS as a foreign key for your other tables.

|improve this answer|||||
  • After a while of thinking, I realized that this solution is best. Now perhaps I still have to define some triggers ON DELETE and ON UPDATE to avoid orphans. Thank you! – Bernd Morgeneyer Apr 12 '18 at 16:21
  • 1
    @BerndMorgeneyer, what do you mean with avoid orphans? Foreign keys guarante that the data is consistent, you can declare on update and on delete actions dependent on what you want to happen during modifications on the parent table. – Lennart Apr 12 '18 at 16:38
  • As you can see in my example (after EDIT), this is not the classical foreign key case. In principle I could define two columns comment_en and comment_de for english and german texts but I think it's better to have one single table with all translations. So I can easily extend my application to french by adding columns for language = 'fr-FR'. – Bernd Morgeneyer Apr 15 '18 at 9:45
  • I don't think I understand the scenario you are describing. Can you give one example of when you would need a trigger to delete something? – Lennart Apr 15 '18 at 10:06
  • The normal way to edit my data would be to update or delete rows in table MyObject. So, without trigger, there woluld be orphans in table Texts after deleting one row. – Bernd Morgeneyer Apr 15 '18 at 10:58
1

You could create an INSERT TRIGGER with the query to verify the data upon insert or you could use a user defined function similar to the example in How to create CHECK CONSTRAINT based on a query?. From that post:

-- we want to check if the values in the following tables are a subset of the main state table
CREATE TABLE t1 (stateId INT NOT NULL)

-- create a function to check the state values and return 1 if stateId is invalid
CREATE FUNCTION dbo.fnc_IsValidState(@StateId INT, @TypeId Int)     RETURNS BIT
AS 
BEGIN 
   DECLARE @flag BIT = 1

   IF NOT EXISTS (SELECT 1 FROM state WHERE stateId =@StateId AND TypeId=@TypeId)
   BEGIN
      SET @flag = 0      
   END   

   RETURN      @flag

 END

 go

--Add a check constraints to the table to call the function 
ALTER TABLE dbo.[t1]  WITH NOCHECK ADD  CONSTRAINT [CK_StateCHeck] CHECK  ((dbo.fnc_IsValidState(StateId,1)=1))

I did find a cautionary post about Be careful with constraints calling UDFs from Tibor Karaszi. From that post:

Be careful with constraints calling UDFs

You might just not get what you think. I would be surprised if this hasn't been blogged already, but if so, it would be worth repeating. Here's the deal (example from a forum,, slightly re-worked):

I want the values in one column to be unique, assuming the value in another column is 1. Can I use an UDF for that?

On the surface, yes. You can write an UDF to wich you pass the value which should be conditionally unique and in that UDF check how many rows has this value AND othercolumn = 1. If more than 1 row, then function returns 0, else 1 (or something else to signal "OK" or "Not OK"). Now, you can call this function in a CHECK constraint. Something like CHECK(myFunction(uniqueCol) = 1). this will on the surface do its job, as long as you INSERT into the table. But if you update a row and only set the otherColumn for some row from 0 to 1, then the check constraint will not be checked. The optimizer is smart enough to understand that the update doesn't change anything that we refer to in our CHECK constraint, so why bother checking the constraint? End result here is that the constraint doesn't do what we want it to do. Use a trigger instead (or some other method).

|improve this answer|||||
  • The error occurs if I use the EXISTS statement directly in my constraint: Subqueries not allowed, only scalar expressions – Bernd Morgeneyer Apr 11 '18 at 10:13
  • 2
    @BerndMorgeneyer Why did you do that, instead of what Scott suggested? Hint: Maybe there is a reason he didn't directly use EXISTS in the constraint. – Aaron Bertrand Apr 11 '18 at 11:52
1

IMO there is no way to define partial key relation .

if PK is composite then FK have to be composite.

Or you hv to break you index if want to define single column as FK like in my example below.

alter table [Text] and change index Example

CREATE TABLE [dbo].[Texts](

    [Id] [int] NOT NULL,
    [language] [nchar](5) NOT NULL,
    [text] [nvarchar](max) NULL,
 )

create clustered index ix_id_name_texts on(texts)(id,language)

create primary key ix_id_texts on(texts)(id)

this pk is non clustered index.

if existing ID column cannot be alone PK then you can introduce one more id identity column as PK.

Define this new id as FK in another column.

I am just trying to show that how you have to change index plan in order to define FK constraint. Those index may or may not be suitable for other purpose.

now you can easily define FK constraint,

CREATE TABLE [dbo].[MyObject](
    [id] [int] NOT NULL,
    [name] [int] NOT NULL,
    [comment] [int] NOT NULL,
 CONSTRAINT [PK_PlantDescription] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)

ALTER TABLE [dbo].[MyObject]
ADD FOREIGN KEY (name) REFERENCES Texts(id); 

ALTER TABLE [dbo].[MyObject]
ADD FOREIGN KEY (comment) REFERENCES Texts(id); 

You can also create Before Insert trigger on [dbo].[MyObject]

Trusted FK constraint and check may give you benefit in query plan. Trigger won't give you this advantage.

IMO, Trigger is more suitable for any important business rule.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.