SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting up SQL to only accept specific data in a specific field


Setting up SQL to only accept specific data in a specific field

Author
Message
akhale123
akhale123
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 17
I have a table that has 2 fields that hold identical data. Lets call them field 1 and 2. Field 2 should pull its data from field 1, but thats not always the case. How can I tell SQL not to accept data in field 2 if it does not exactly match the data in field 1 for each record?
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59433 Visits: 9730
Why even have field2? If it's the same as field1, why store the data twice?

What you're asking for is most likely a check constraint. If you look up "constraints" in Books Online, it has samples and suggestions on the subject.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74377 Visits: 40977
it sounds like if Field 2 is supposded to be a part of field 1, it should either be a calculated column instead, or have a check constraint.

create table #example(exampleId int identity(1,1) not null primary key,
exampletext varchar(30),
myCalculatedField as left(exampletext,3), --first 3 characters?
myRestrictedField (varchar 3 CHECK (myRestrictedField=LEFT(exampletext,3) ) --must equal part of another field
)

edited to say i saw GSquared's post after i posted...great minds think alike.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
ab5sr
ab5sr
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 704
I agree, not sure why you would ever want the same data in another column; maybe you should make it null or add a placeholder, and then do some calculation and populate later?

You might do something like this?

DROP TABLE #example
GO
CREATE TABLE #example(exampleId int identity(1,1) NOT NULL PRIMARY KEY,
exampletext varchar(30),
myCalculatedField AS SUBSTRING (ExampleText, CHARINDEX(':', exampleText )+1, len(exampleText)),
myCalcd2 AS UPPER(LEFT(SUBSTRING (ExampleText, CHARINDEX(':', exampleText )+1, len(exampleText)),4))
)
GO
INSERT INTO #example (exampletext)
VALUES ('Product Description: Chair' )
GO
SELECT * FROM #example

GO



Lee Everest


akhale123
akhale123
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 17
Field A is the account number (AP account # or AR account #)that is selected by the user when doing a transaction
Field 2 is the cash account, which the money is pulled from or put into when the account is selected in field A. Field 2 is transparent to the user. This data is in TableA (which is the general transaction table).

Field 2 is then appended to the table B ( which is the history table).
For some reason, we find field 2 in the tableB referencing the wrong account #.
How can we set it to where, when field 2 do is appended in the tableB, SQl needs to verify that is it the similar to field 1 in table A
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search