Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting up SQL to only accept specific data in a specific field Expand / Collapse
Author
Message
Posted Monday, February 23, 2009 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 26, 2009 6:33 AM
Points: 6, 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?
Post #662803
Posted Monday, February 23, 2009 11:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #662816
Posted Monday, February 23, 2009 11:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:11 PM
Points: 12,922, Visits: 32,274
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #662837
Posted Wednesday, February 25, 2009 5:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 215, Visits: 652
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

Post #664148
Posted Wednesday, February 25, 2009 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 26, 2009 6:33 AM
Points: 6, 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
Post #664225
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse