Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

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)
0
brgdotnet
Asked:
brgdotnet
1 Solution
 
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
 
brgdotnetAuthor 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
brgdotnetAuthor 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
 
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
 
brgdotnetAuthor Commented:
Thanks Vitor, you answered my questions perfectly. Have a great week!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now