Solved

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

Posted on 2014-11-16
9
88 Views
Last Modified: 2014-11-17
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
Comment
Question by:brgdotnet
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 2

Author Comment

by:brgdotnet
Comment Utility
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
 
LVL 2

Author Comment

by:brgdotnet
Comment Utility
Specifically can a check constraint be used in this case?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 2

Author Closing Comment

by:brgdotnet
Comment Utility
Thanks Vitor, you answered my questions perfectly. Have a great week!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now