Using CHECK constraints for unique characters and from other columns PostgresSQL
I have a designed table called STAFF with 3 columns for use in PostgreSQL.
staffId VARCHAR(6) PK,
firstName VARCHAR(50),
lastName VARCHAR(50)
I would like to build a CHECK constraint so that the first 2 letters of staffId must match the first letter of firstName and the first letter of lastName, and that the last four numbers cannot be the same as another entry in staffId.
E.g.
John Smith - JS0001,
Lisa Jones - LJ0002
so if Joanne Smith were to join the team then JS0002 would fail due to Lisa Jones already having 0002.
How can I do this?
Many thanks.
sql postgresql constraints
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
add a comment |
I have a designed table called STAFF with 3 columns for use in PostgreSQL.
staffId VARCHAR(6) PK,
firstName VARCHAR(50),
lastName VARCHAR(50)
I would like to build a CHECK constraint so that the first 2 letters of staffId must match the first letter of firstName and the first letter of lastName, and that the last four numbers cannot be the same as another entry in staffId.
E.g.
John Smith - JS0001,
Lisa Jones - LJ0002
so if Joanne Smith were to join the team then JS0002 would fail due to Lisa Jones already having 0002.
How can I do this?
Many thanks.
sql postgresql constraints
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
You shouldn't be storing that prefix to begin with. If it's always the same, just create a view that concatenates that information
– a_horse_with_no_name
Nov 24 '18 at 10:55
Is what I'm looking to achieve something that wouldn't normally be done?
– Lee Powell
Nov 24 '18 at 11:26
One rule of good database design is to not store information that can (easily) be derived from existing information.
– a_horse_with_no_name
Nov 24 '18 at 11:37
add a comment |
I have a designed table called STAFF with 3 columns for use in PostgreSQL.
staffId VARCHAR(6) PK,
firstName VARCHAR(50),
lastName VARCHAR(50)
I would like to build a CHECK constraint so that the first 2 letters of staffId must match the first letter of firstName and the first letter of lastName, and that the last four numbers cannot be the same as another entry in staffId.
E.g.
John Smith - JS0001,
Lisa Jones - LJ0002
so if Joanne Smith were to join the team then JS0002 would fail due to Lisa Jones already having 0002.
How can I do this?
Many thanks.
sql postgresql constraints
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
I have a designed table called STAFF with 3 columns for use in PostgreSQL.
staffId VARCHAR(6) PK,
firstName VARCHAR(50),
lastName VARCHAR(50)
I would like to build a CHECK constraint so that the first 2 letters of staffId must match the first letter of firstName and the first letter of lastName, and that the last four numbers cannot be the same as another entry in staffId.
E.g.
John Smith - JS0001,
Lisa Jones - LJ0002
so if Joanne Smith were to join the team then JS0002 would fail due to Lisa Jones already having 0002.
How can I do this?
Many thanks.
sql postgresql constraints
sql postgresql constraints
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
edited Nov 24 '18 at 12:07
Gordon Linoff
780k35309412
780k35309412
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
asked Nov 24 '18 at 10:44
Lee PowellLee Powell
193
193
You shouldn't be storing that prefix to begin with. If it's always the same, just create a view that concatenates that information
– a_horse_with_no_name
Nov 24 '18 at 10:55
Is what I'm looking to achieve something that wouldn't normally be done?
– Lee Powell
Nov 24 '18 at 11:26
One rule of good database design is to not store information that can (easily) be derived from existing information.
– a_horse_with_no_name
Nov 24 '18 at 11:37
add a comment |
You shouldn't be storing that prefix to begin with. If it's always the same, just create a view that concatenates that information
– a_horse_with_no_name
Nov 24 '18 at 10:55
Is what I'm looking to achieve something that wouldn't normally be done?
– Lee Powell
Nov 24 '18 at 11:26
One rule of good database design is to not store information that can (easily) be derived from existing information.
– a_horse_with_no_name
Nov 24 '18 at 11:37
You shouldn't be storing that prefix to begin with. If it's always the same, just create a view that concatenates that information
– a_horse_with_no_name
Nov 24 '18 at 10:55
You shouldn't be storing that prefix to begin with. If it's always the same, just create a view that concatenates that information
– a_horse_with_no_name
Nov 24 '18 at 10:55
Is what I'm looking to achieve something that wouldn't normally be done?
– Lee Powell
Nov 24 '18 at 11:26
Is what I'm looking to achieve something that wouldn't normally be done?
– Lee Powell
Nov 24 '18 at 11:26
One rule of good database design is to not store information that can (easily) be derived from existing information.
– a_horse_with_no_name
Nov 24 '18 at 11:37
One rule of good database design is to not store information that can (easily) be derived from existing information.
– a_horse_with_no_name
Nov 24 '18 at 11:37
add a comment |
1 Answer
1
active
oldest
votes
This doesn't seem like good database design. You should simply declare StaffId as a serial column and let it be an integer. Voila! That's done.
Integers are generally more efficient than strings for foreign key references. They also uniquely identify staff, even when their name changes.
For instance, in the country where I live women (often) and men (sometimes) change their names when they get married or divorced. That would totally throw off your scheme.
All that said, you can do what you want with unique and check constraints:
alter table staff add constraint chk_staffId
check (substr(staffId, 1, 1) = substr(firstname, 1, 1) and
substr(staffId, 2, 1) = substr(lastname, 1, 1) and
staffId ~ '^[A-Z][A-Z][0-9]{4}$'
);
Then the constraint would be implemented as an index on an expression:
create index unq_staffId_number on (substr(staffid, 3, 4));
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53457330%2fusing-check-constraints-for-unique-characters-and-from-other-columns-postgressql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This doesn't seem like good database design. You should simply declare StaffId as a serial column and let it be an integer. Voila! That's done.
Integers are generally more efficient than strings for foreign key references. They also uniquely identify staff, even when their name changes.
For instance, in the country where I live women (often) and men (sometimes) change their names when they get married or divorced. That would totally throw off your scheme.
All that said, you can do what you want with unique and check constraints:
alter table staff add constraint chk_staffId
check (substr(staffId, 1, 1) = substr(firstname, 1, 1) and
substr(staffId, 2, 1) = substr(lastname, 1, 1) and
staffId ~ '^[A-Z][A-Z][0-9]{4}$'
);
Then the constraint would be implemented as an index on an expression:
create index unq_staffId_number on (substr(staffid, 3, 4));
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
add a comment |
This doesn't seem like good database design. You should simply declare StaffId as a serial column and let it be an integer. Voila! That's done.
Integers are generally more efficient than strings for foreign key references. They also uniquely identify staff, even when their name changes.
For instance, in the country where I live women (often) and men (sometimes) change their names when they get married or divorced. That would totally throw off your scheme.
All that said, you can do what you want with unique and check constraints:
alter table staff add constraint chk_staffId
check (substr(staffId, 1, 1) = substr(firstname, 1, 1) and
substr(staffId, 2, 1) = substr(lastname, 1, 1) and
staffId ~ '^[A-Z][A-Z][0-9]{4}$'
);
Then the constraint would be implemented as an index on an expression:
create index unq_staffId_number on (substr(staffid, 3, 4));
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
add a comment |
This doesn't seem like good database design. You should simply declare StaffId as a serial column and let it be an integer. Voila! That's done.
Integers are generally more efficient than strings for foreign key references. They also uniquely identify staff, even when their name changes.
For instance, in the country where I live women (often) and men (sometimes) change their names when they get married or divorced. That would totally throw off your scheme.
All that said, you can do what you want with unique and check constraints:
alter table staff add constraint chk_staffId
check (substr(staffId, 1, 1) = substr(firstname, 1, 1) and
substr(staffId, 2, 1) = substr(lastname, 1, 1) and
staffId ~ '^[A-Z][A-Z][0-9]{4}$'
);
Then the constraint would be implemented as an index on an expression:
create index unq_staffId_number on (substr(staffid, 3, 4));
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
This doesn't seem like good database design. You should simply declare StaffId as a serial column and let it be an integer. Voila! That's done.
Integers are generally more efficient than strings for foreign key references. They also uniquely identify staff, even when their name changes.
For instance, in the country where I live women (often) and men (sometimes) change their names when they get married or divorced. That would totally throw off your scheme.
All that said, you can do what you want with unique and check constraints:
alter table staff add constraint chk_staffId
check (substr(staffId, 1, 1) = substr(firstname, 1, 1) and
substr(staffId, 2, 1) = substr(lastname, 1, 1) and
staffId ~ '^[A-Z][A-Z][0-9]{4}$'
);
Then the constraint would be implemented as an index on an expression:
create index unq_staffId_number on (substr(staffid, 3, 4));
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
answered Nov 24 '18 at 12:14
Gordon LinoffGordon Linoff
780k35309412
780k35309412
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53457330%2fusing-check-constraints-for-unique-characters-and-from-other-columns-postgressql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You shouldn't be storing that prefix to begin with. If it's always the same, just create a view that concatenates that information
– a_horse_with_no_name
Nov 24 '18 at 10:55
Is what I'm looking to achieve something that wouldn't normally be done?
– Lee Powell
Nov 24 '18 at 11:26
One rule of good database design is to not store information that can (easily) be derived from existing information.
– a_horse_with_no_name
Nov 24 '18 at 11:37