How to build a constraint on a column when an insert in performed.

Suppose I have two tables, as listed below
How can I put a constraint on the "FullName" column of Table_2, such that
that FullName in Table_2 never contains a name which is the same as a first name which might exists in the FirstName column of Table_1?
The purpose is to prevent someone from entering just a first name in the FullName column, instead they should be entering two names for the full name FirstName + LastName.

Ignore the fact that someone might just have one name (Sausquach, Prince, Bono) such as a performer.
In this hypothetical situation I need a first name plus a last name.

So how does one go about building such a constraint? Would I need a sql script of function to query the FirstName column of Table_1, each time a FullName value is entered into the FullName column of table 2 ?


Table_1 : (FirstName and LastName are the primary key)
UserId
FirstName
LastName


Table_2
NameId
FullName  (Put a constraint on this column, so that the a first name never appears in this column)
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Drawing a blank on that one, but for starters HOLY FREEEAKING GAWD change the primary key in Table_1 to something other than FirstName + LastName, such as UserId.

For example, there is a 6'3" bald saxophone player named Jim Horn that hocks his CD's on the Internet.
I am not that guy, and if one of us makes it into the table, the other would not be allowed in based on a FirstName + LastName primary key.   I once had a boss that had a namesake that was a Level 3 sex offender in Kentucky.  Imagine that dogging you forever..

Also, what is the purpose of having the same name values in Table_1 and Table_2?  If you need the full name just use Table_1.FirstName + ' ' + Table_2.LastName.  Otherwise you have the same data in multiple locations, which is an error waiting to happen.

Also, this smells like something that should/could be handled in the UI, and not the database.
0
HainKurtSr. System AnalystCommented:
I am a bit lost :) but this one does not look nice a nice design and just screams issues...

I suggest this

Table_1 : (FirstName and LastName are the primary key)
UserID (PK)
FirstName
LastName

(add a unique index on FirstName + LastName)

Table_2
UserID (FK that points to Table_1.UserID)
Other related data that belongs to UserID, no need to store FirstName & LastName here...
0
brgdotnetcontractorAuthor Commented:
This is a hypothetical question, and not a specific implementation. The heart of my question is in regards to if a constraint can be put in place to constrain values inserted into a particular column. Namely the constraint would involve querying a column in a separate table before the insert occurs.

So please read the question in regards to setting a constraint on a column. Possibly the question would best be answered by someone who is familiar with placing constraints on a table column, when an insert occurs on a specific column.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brgdotnetcontractorAuthor Commented:
Specifically can a check constraint be used in this case?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think that you can't do that directly (Constraint querying another table) but you can build a function called for example CheckFullname and inside that function you write the query that will do the check for you. Then you just need to run the following command:
alter table YourTableNameHere
add constraint chk_CheckFullname
check (dbo.CheckFullname() = 1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>This is a hypothetical question, and not a specific implementation.
For future reference please stick to asking actual questions and not hypothetical questions, as EE has a policy against homework, a hypothetical question screams homework, and there are multiple experts here with Admin rights that are trained to discourage this behavior.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Possibly the question would best be answered ....
Also keep in mind that many experts here have many years of experience and Microsoft certifications, and if we say that there are design problems beyond the exact question, then that is usually sound advice and should be considered.
0
Scott PletcherSenior DBACommented:
A constraint cannot directly reference other tables.  In fact, it cannot even reference other rows in the same table.

You would have to use a trigger or a function as a constraint.
0
brgdotnetcontractorAuthor Commented:
Thanks Vitor, you answered my questions perfectly. Have a great week!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.