February 23, 2009 at 11:14 am
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?
February 23, 2009 at 11:29 am
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
February 23, 2009 at 11:40 am
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
February 25, 2009 at 5:43 am
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
February 25, 2009 at 7:22 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply