Table column references partial key of table B 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?
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.