August 6, 2017 at 9:40 pm
Comments posted to this topic are about the item Using Super Keys to Enforce Database Constraints
August 7, 2017 at 1:56 am
Looking at this I feel that the example starts off denormalised. Rather than have a guide with a language attribute and a team with a language attribute I would have
This allows a guide to speak multiple languages and multiple languages within the team. Highly likely if the guide is non-British and extremely likely given the composition of an English football team.
If you want to enforce a single language rule then put the primary key for guidelanguage on guide and teamlanguage on team.
The guidelanguage and teamlanguage can then relate to teamguides if you put the compound key for both in teamguides.
The rejection of records where a guide and team do not speak the same language can be achieved with a check constraint.
It may be a bit nit picky but there are no primary keys in the code mentioned. A UNIQUE constraint is not a PRIMARY key constraint. You could simply replaced UNIQUE with PRIMARY KEY. As we use Red-Gate SQL Compare we prefer to name our constraints explicitly rather than have system assigned names. To give and example.
CREATE TABLE Guides(
Guide varchar (15) NOT NULL ,
LangSpoken varchar (3) NOT NULL,
CONSTRAINT PK_Guides PRIMARY KEY NONCLUSTERED(Guide)
);
or CREATE TABLE Guides(
Guide varchar (15) NOT NULL
CONSTRAINT PK_Guides PRIMARY KEY NONCLUSTERED,
LangSpoken varchar (3) NOT NULL
);
In terms of join performance SQL Server and ORACLE would not be bothered by 1 or 2 joins. The use of VARCHAR(15) instead of a surrogate key would be likely to have a greater impact but unless you had a huge number of records this would produce little observable difference to the supporting application.
August 7, 2017 at 3:37 am
Looking at this I feel that the example starts off denormalised.
The usual reaction of an DBA fits again: it depends. The source for the language codes is not clearly stated in the example. It might be any (unrestricted) combination of zero to three characters chosen by the end user. It might be an internal enumeration used within the application code. It might be a three character (upper case letters) code chosen from a fixed list. Or entirely something else. We can not know this from the article text.
If the user may chose any combination from zero to three characters for a language code, the model is indeed in normal form. Maybe the codes used in the example give the wrong impression about the freedom of choice for these language codes but that does not invalidate the model. If the codes come from an enumeration, this could be modeled in several ways, like a foreign key to a table with allowed language codes, or a hard-coded constraint on every language code column.
I do agree that using primary keys instead of unique constraints for the actual key of a table would improve the example. Naming keys is not always needed in an example, however the author does name most of the constraints end, and a more consistent naming convention could improve the example, even for a novice DBA or database designer. But the example fits its purpose perfectly, showing how a complex real life constraint can be enforced in a database model using superkeys without the use of complex triggers. This adds a general formal method for incorporating these constraints in other databases and the added value of such method outweighs the minor issues mentioned earlier. Articles offering general solutions to common problems are rare, and should be pampered and praised because they do expand the toolbox of any database designer. Thanks a lot, Dejan!
August 7, 2017 at 4:29 am
I agree with Zidar, an uncomplicated explanation of superkeys. Of course there are always other ways to do things and complications to add on (ask a watchmaker) but, if the purpose of the article was to help us to understand superkeys, then it succeeded magnificently.
Of course, being a developer, I have to have one little nit pic; the default ENG when adding language to the team guides table is unnecessary. Even though it is a non null column, you have already emptied the table so it will add without a default without complaint. As I say - picky.
Thanks very much for this, I will definitely use it.
August 7, 2017 at 7:13 am
I thought this was great. Learned something today. Thank you.
August 7, 2017 at 9:54 am
although rushed a more complete solution would involve extra tables for languages, Team languages, Guide languages and the Tour Day adding Language for the day: something like
if object_id('TourDay') is not null
drop table TourDay;
go
if object_id('TeamLanguage') is not null
drop table TeamLanguage;
go
if object_id ('GuideLanguage') is not null
drop table GuideLanguage
go
if object_id('Teams') is not null
drop table Teams;
go
if object_id ('Guides') is not null
drop table Guides
go
if object_id('Languages') is not null
drop table Languages;
go
create table Languages
(Lang char(3),
LanguageName varchar(20),
constraint Languages_pk primary key (Lang)
)
go
insert Into Languages (Lang,LanguageName) values
('ENG','English'),
('NLA','Dutch'), --
('FRA','French'),
('GER','German'),
('RUS','Russian'),
('DEU','German');
go
create table Teams
(
TeamId char(3),
TeamName varchar(20),
constraint Teams_pk primary key (TeamId)
)
go
insert into Teams (TeamId,TeamName)
values
('FRA','France'),
('ENG','England'),
('NLA','Holand'),
('MOR','Morocco'),
('DEU','Germany'),
('RUS','Russia'),
('USA','America');
go
create table TeamLanguage
(
TeamId CHAR(3),
TeamLanguage char(3)
constraint TeamLanguage_pk primary key ( TeamId,TeamLanguage),
constraint TeamLanguage_fk_Team foreign Key (TeamId) references Teams (TeamId),
constraint TeamLanguage_fk_Lang foreign Key (TeamLanguage) references Languages(Lang)
)
go
insert into TeamLanguage (TeamId,TeamLanguage)
SELECT TeamId ,'ENG' from Teams where TeamName in ('England','America','Holand');
insert into TeamLanguage (TeamId,TeamLanguage)
SELECT TeamId ,'NLA' from Teams where TeamName in ('Holand');
insert into TeamLanguage (TeamId,TeamLanguage)
SELECT TeamId ,'FRA' from Teams where TeamName in ('France','Morocco');
insert into TeamLanguage (TeamId,TeamLanguage)
SELECT TeamId ,'RUS' from Teams where TeamName in ('Russia');
insert into TeamLanguage (TeamId,TeamLanguage)
SELECT TeamId ,'DEU' from Teams where TeamName in ('Germany','Russia');
select * from TeamLanguage
go
create table Guides
(
GuideId int identity(1,1),
GuideName varchar(100),
constraint Guides_pk primary key (GuideId)
)
go
INSERT INTO Guides
(GuideName)
VALUES
('Julles'),
('William'),
('Regine'),
('Donald'),
('Sergey'),
('Heinz'),
('Ronald')
go
create table GuideLanguage
(GuideId int,
GuideLanguage char(3),
constraint GuideLanguage_pk primary key (GuideId,GuideLanguage),
constraint GuideLanguage_fk_Guide foreign key (GuideId) references Guides ( GuideId),
constraint GuideLanguage_fk_Lang foreign Key (GuideLanguage) references Languages(Lang)
)
go
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'ENG' FROM Guides where GuideName = 'William';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'NLA' FROM Guides where GuideName = 'William';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'DEU' FROM Guides where GuideName = 'William';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'ENG' FROM Guides where GuideName = 'Donald';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'FRA' from Guides where GuideName = 'Regine';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'FRA' from Guides where GuideName = 'Julles';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'DEU' from Guides where GuideName = 'Heinz';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'RUS' from Guides where GuideName = 'Sergey';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'NLA' from Guides where GuideName = 'Ronald';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'ENG' from Guides where GuideName = 'Ronald';
insert into GuideLanguage (GuideId,GuideLanguage) select GuideId,'DEU' from Guides where GuideName = 'Ronald';
GO
create table TourDay
(
TeamId char(3) not null,
TourDay int not null,
GuideId int null,
Lang char(3) null
constraint TourDay_pk primary key (TeamId,TourDay),
constraint TourDay_fk_Team foreign key (TeamId) references Teams (TeamId),
constraint TourDay_fk_Guide foreign key (GuideId) references Guides (GuideId),
constraint TourDay_fk_TeamLang foreign key (TeamId,Lang) references TeamLanguage (TeamId,TeamLanguage),
constraint TourDay_fk_GuideLang foreign key (GuideId,Lang) references GuideLanguage(GuideId,GuideLanguage)
);
go
August 8, 2017 at 1:03 am
Thanks, learned something
August 8, 2017 at 9:24 am
I don't think of these as "super keys" per se, but rather COMPOSITE keys. Which from a database standpoint is exactly what you would do. But more importantly from a domain modeling perspective, this is the heart of the matter of impedance mismatch, wherein one needs additional information to describe the RELATIONSHIP among two or more elements. In this case, further constraining the relationship according to language.
August 8, 2017 at 2:04 pm
I want to thank everybody for useful observations and comments, this is my first article, and all comments mean a lot to me 🙂
The idea was to focus on using unorthodox method for enforcing certain business rules. That is why the example is bare bones. I agree with all observations - naming conventions could and should be better, domains for languages, teams and guides should have been dealt with in a more precise and consistent way. I was afraid of losing the main point - referencing super keys instead of unique keys in order to enforce specific business rule declarative way. Obviously, I failed to minimize confusion, rather I messed up, and apologize for that. To my defense, this was exercise in logical rather than physical design. And also my first article for SQL Central. I’ll get better, promise.
I understand now that lack of explicitly declared primary keys confuses people, and will take care of it next time. No worries, primary key do not have to be explicitly declared, as long as we have at least one unique key defined (no Nulls allowed).
For DEFAULT (‘Eng’), I agree, another bad choice. The reason was ALTER TABLE ADD <column> command. I needed NOT NULL column, and ALTER TABLE ADD <column> does not allow NOT NULL declaration, unless we declare DEFAULT. Anything would do for the default, and I thought that ‘Eng’ is as good as anything else. Now I know better.
As for question whether super keys are actually composite keys, we need to clarify that. Super key is any combination of one or more attributes, (columns) that uniquely determine a tuple (row) in given relation (table). For example, imagine table Players (PlayerID (PK), Fname, Lname, BirthDate). It has exactly one key, PlayerID, which we duly chose for Primary Key. There are many potential super keys. Some of them are: (PlayerID, Fname), (PlayerID, Lname), (PlayerID, Fname, Lname), (PlayerID, BirthDate), (PlayerID, Fname, Lname, BirthDate) … Any combination of one or more columns is a super key, as long as one of the columns is PK column. Combination (PlayerID) is also a super key - ONE column, PK column itself. It turns out that PlayerID is both key and super key at the same time. It follows that every key is a super key, but not every super key is a key. Super key that is not a key is called proper super key. Proper super key has more columns than needed for uniqueness.
Super keys I used in the example are proper super keys:
CREATE TABLE Teams
(
Team varchar (15) NOT NULL
, LangSpoken varchar (3) NOT NULL
, CONSTRAINT PK_Teams PRIMARY KEY (Team)
, CONSTRAINT Superkey_Teams UNIQUE (Team, LangSpoken)
)
;;
Constraint Superkey_Teams is a super key, it is unique, and has one extra column, LangSpoken. It cannot be a key – remember key is irreducible, proper super key is reducible. It does look like composite key, but it is not. If we wanted to track teams over several competitions, we could have used this design:
CREATE TABLE Teams
(
Competition varchar (15) NOT NULL-– like World Cup 2018, Olympics 1964
, Team varchar ((15)) NOT NULL
, LangSpoken varchar ((3)) NOTNOT NULLNULL
, CONSTRAINT PK_Teams PRIMARY KEY (Team, Competition)
, CONSTRAINT Superkey_Teams UNIQUE (Team, Competition, LangSpoken)
)
;;
Primary key (Team, Competition) is composite, because it has two columns.
The other constraint, (Team, Competition, LangSpoken) is super key, with one extra column, LangSpoken. It contains the key (composite in this case).
Why bother with all of that, when it is so confusing and convoluted? Well, that’s what the article was about. We can use super keys in situations when unique key is not sufficient. It turns out that sometimes extra attributes are useful.
I hope these clarified things, at least a bit.
Cheers and thank you again for the comments.
August 8, 2017 at 3:07 pm
To be clear, I don't see this as an unorthodox way of handling so-called impedance mismatches. Indeed, there are times when one WANTS to capture properties of the RELATIONSHIP on the JOINING table(s), and NOT on the JOINED tables, such as in this case language, or sometimes date/time information, such as activation or expiration dates, or things such as these.
Cheers,
Michael
October 8, 2019 at 10:31 am
Thanks for this example of using referential integrity constraints that reference superkeys ... just what I was looking for.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply