June 7, 2016 at 1:41 pm
Listed below is some code taken from ORACLE.
Take a look at the part "ALTER TABLE....
I like to know how to do the same in T-SQL.
Basically I have a VARCHAR(10) field. I was to make sure the values are populated in the
xx/xx/xxxx format
Ex: 05/20/1964
How do I enforce that rule ?
DROP TABLE int_mm_member;
CREATE TABLE int_mm_member
(
ProductID VARCHAR2 (50),
MemberID VARCHAR2 (100),
UniversalMemberID VARCHAR2 (150),
MedicareID VARCHAR2 (12),
MedicaidID VARCHAR2 (30),
SSN VARCHAR2 (11),
Confidential VARCHAR2 (1),
NameLast VARCHAR2 (60),
NameFirst VARCHAR2 (35),
NameMiddleInitial VARCHAR2 (1),
NameSuffix VARCHAR2 (10),
DOB CHAR (10),
DOD CHAR (10),
Gender VARCHAR2 (1),
Race VARCHAR2 (50),
ContactLastName VARCHAR2 (60),
ContactFirstName VARCHAR2 (35),
ContactMiddleInitial VARCHAR2 (1),
ContactGender VARCHAR2 (1),
ContactAddress1 VARCHAR2 (55),
ContactAddress2 VARCHAR2 (55),
ContactCity VARCHAR2 (30),
ContactCounty VARCHAR2 (50),
ContactState VARCHAR2 (2),
ContactZipCode VARCHAR2 (15),
ContactTelephone VARCHAR2 (13),
Language VARCHAR2 (50),
AltLanguage1 VARCHAR2 (50),
AltLanguage2 VARCHAR2 (50),
Hispanic VARCHAR2 (1),
Interpreter VARCHAR2 (1),
LanguageSource VARCHAR2 (25),
WrittenLanguage VARCHAR2 (50),
WrittenLanguageSource VARCHAR2 (25),
OtherLanguage VARCHAR2 (50),
OtherLanguageSource VARCHAR2 (25),
RaceSource VARCHAR2 (25),
EthnicitySource VARCHAR2 (25),
MemberCustom1 VARCHAR2 (50),
MemberCustom2 VARCHAR2 (50),
MemberCustom3 VARCHAR2 (50),
MemberCustom4 VARCHAR2 (50),
CitizenshipStatus CHAR (1),
AsOfDate CHAR (10),
SourceID VARCHAR2 (25) NOT NULL,
CONSTRAINT pk_int_mm_member PRIMARY KEY (ProductID, MemberID)
);
ALTER TABLE int_mm_member
ADD
CONSTRAINT dt_int_mm_member
CHECK (
( dob IS NULL OR dob LIKE '__/__/____')
AND (dod IS NULL OR dod LIKE '__/__/____')
AND (AsOfDate IS NULL OR AsOfDate LIKE '__/__/____')
);
June 7, 2016 at 1:46 pm
Just replace VARCHAR2 with varchar, but why are you going to all that trouble? Just use a date datatype.
If you have a date of 03/04/2016 is that the 3rd April or the 4th March?
June 7, 2016 at 3:29 pm
3rd of April, of course.
But yes, dates in datetime, datetime2 or one of SQL's other datetime data types and format for presentation. Makes date comparisons and ordering much easier (unless you like the 10th of April to be considered before the 3rd April)
The constraint definition syntax is near identical in SQL and Oracle, at least for stuff like this. You can do it in the CREATE TABLE or with
ALTER TABLE <tbl name> ADD CONSTRAINT <constraint name> dob LIKE '__/__/____'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply