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.

edited Nov 24 '18 at 12:07

Gordon Linoff

780k35309412

asked Nov 24 '18 at 10:44

Lee 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)

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.

edited Nov 24 '18 at 12:07

Gordon Linoff

780k35309412

asked Nov 24 '18 at 10:44

Lee 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)

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.

edited Nov 24 '18 at 12:07

Gordon Linoff

780k35309412

asked Nov 24 '18 at 10:44

Lee 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)

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 Powell

193

edited Nov 24 '18 at 12:07

Gordon Linoff

780k35309412

asked Nov 24 '18 at 10:44

Lee 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

asked Nov 24 '18 at 10:44

Lee Powell

193

asked Nov 24 '18 at 10:44

Lee Powell

193

asked Nov 24 '18 at 10:44

Lee 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 |

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

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 |

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 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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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 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 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 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 Linoff

780k35309412

answered Nov 24 '18 at 12:14

Gordon Linoff

780k35309412

answered Nov 24 '18 at 12:14

Gordon Linoff

780k35309412

answered Nov 24 '18 at 12:14

Gordon Linoff

780k35309412

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk