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

Question

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

akhale123

SSC Veteran

Points: 240
More actions
February 23, 2009 at 11:14 am

#201484

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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 SSC Guru Points: 323518 More actions · Answer 2

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 SSCarpal Tunnel Points: 4056 More actions · Answer 3

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

akhale123 SSC Veteran Points: 240 More actions · Answer 4

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