The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.
The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .
Source: A Relational Model of Data for Large Shared Data Banks
Codd retained that term. (Same source.)
Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key.
It's not unusual for a relation to have more than one "combination of domains" that uniquely identify each tuple. The relational model offers no theoretical basis for elevating the status of one of them to "primary". As far as the relational model is concerned, all candidate keys are created equal.
As far as standard SQL is concerned, any column or combination of columns that's declared UNIQUE can be the target of a foreign key constraint. In the past, dbms platforms differed slightly in how they handled NULL in a column declared UNIQUE. That's part of the reason old-school database designers lean toward NOT NULL UNIQUE declarations; the other part is that NOT NULL UNIQUE is functionally equivalent to PRIMARY KEY.
There is a semantic difference between a UNIQUE index and a UNIQUE constraint. I prefer to see constraints expressed as constraints.
If I understand your requirements correctly....
I would just use the natural key, LanguageCode-CultureCode ("en-US," for example). It's small enough. (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)
CREATE TABLE [dbo].[Language](
[Language] [char](2) NOT NULL,
[Culture] [char](2) NOT NULL,
[LanguageCode] AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[Language] ASC,
[Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED
(
[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Language_Text](
[LanguageID] [varchar](5) NOT NULL,
[LanguageCode] [varchar](5) NOT NULL,
[LanguageName] [nvarchar](20) NULL,
CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED
(
[LanguageID] ASC,
[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Language_Text] WITH CHECK ADD CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO
ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO
This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE. Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.
Edit--I'm sorry, you're right, no search by Culture in my old code. Revamped above, sorry. Here's a sample of content:
Language:
Language-Culture-LanguageCode
en US en-US
sv SE sv-SE
Language_Text:
LanguageID-LanguageCode-LanguageName
en-US en-US English
en-US sv-SE Engelska
sv-SE en-US Swedish
sv-SE sv-SE Svenska
Searching by Culture (Canada):
SELECT test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM test.dbo.Language_Text INNER JOIN
test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE (test.dbo.Language.Culture = 'CA')
Searching by Language (French):
SELECT test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM test.dbo.Language_Text INNER JOIN
test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE (test.dbo.Language.Language = 'fr')
Searching by LanguageCode (Swedish):
SELECT LanguageName
FROM [test].[dbo].[Language_Text]
where (LanguageID = 'sv-SE')
Best Answer
UML class diagrams do not include relations in a class' attributes list, so when creating database tables representing those classes, the database designer must identify and specify them, much like the software developer must create the pointers and lists to support the related objects.
In your diagram,
Commande
relates toClient
but does not explicitly specify aIDClient
attribute. When creating theCommande
table, you must include this attribute along with a foreign key toClient
.You can find further information about this in Database Design - UML/SQL: