Solved

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

Posted on 2014-11-16
9
96 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
ID: 40446491
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
ID: 40446533
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
ID: 40446572
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Author Comment

by:brgdotnet
ID: 40446580
Specifically can a check constraint be used in this case?
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40447010
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
ID: 40447387
>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
ID: 40447441
>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:Scott Pletcher
ID: 40448146
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
ID: 40449050
Thanks Vitor, you answered my questions perfectly. Have a great week!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS Deployment problem 5 66
Location of Dynamics AX Service accounts in SQL 3 16
sql server query 18 40
SQL Syntax Grouping Sum question 7 27
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

829 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