Syntax help on how to create a rule

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  December 23, 2015 at 5:43 pm

  #306559

  Create table x

  (

  IN_TABLE_NAME varchar(50),

  SOURCE_TYPE CHAR(1)

  );

  Question: I only want the SOURCE_TYPE column to have the

  following values: P, S, N, M

  How do I create a rule so that the table will only accept those 4 values

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  December 23, 2015 at 6:02 pm

  #1847863

  mw112009 (12/23/2015)

  ---

  Create table x

  (

  IN_TABLE_NAME varchar(50),

  SOURCE_TYPE CHAR(1)

  );

  Question: I only want the SOURCE_TYPE column to have the

  following values: P, S, N, M

  How do I create a rule so that the table will only accept those 4 values

  Create another table (reference table) that contains those 4 values and the explanation of what they are. Make the PK of the table on the 4 values and put an FK on table x to reference the new reference table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  December 24, 2015 at 2:45 am

  #1847886

  I second Jeff's suggestion in most cases but there are times when an additional table might be an overkill such as y/N for YES / NO etc. In those cases one can simply use a check constraint.

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.TBL_CONSTRAINT_COL') IS NOT NULL DROP TABLE dbo.TBL_CONSTRAINT_COL;

  CREATE TABLE dbo.TBL_CONSTRAINT_COL

  (

  CC_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_CONSTRAINT_COL_CC_ID PRIMARY KEY CLUSTERED

  -- SINGLE CHARACTER COLUMN CONSTRAINT TO HOLD ONLY THE VALUE OF FOUR DIFFERENT CHARACTERS, N,M,P AND S

  ,CC_CODE CHAR(1) NOT NULL CONSTRAINT CHKCNST_DBO_CONSTRAINT_COL_CC_CODE CHECK (CC_CODE LIKE '[NMPS]')

  );

  Testing the correct values

  -- TEST THE CORRECT VALUES

  BEGIN TRY

  INSERT INTO dbo.TBL_CONSTRAINT_COL(CC_CODE)

  VALUES ('N'),('M'),('P'),('S');

  END TRY

  BEGIN CATCH

  SELECT ERROR_NUMBER() AS E_NO, ERROR_MESSAGE() AS E_MSG;

  END CATCH

  --VERIFY THE INSERT DID WORK

  SELECT

  CC.CC_ID

  ,CC.CC_CODE

  FROM dbo.TBL_CONSTRAINT_COL CC;

  CC_ID CC_CODE

  ----------- -------

  1 N

  2 M

  3 P

  4 S

  Testing an incorrect value

  -- TEST INCORRECT VALUE

  BEGIN TRY

  INSERT INTO dbo.TBL_CONSTRAINT_COL(CC_CODE)

  VALUES ('A');

  END TRY

  BEGIN CATCH

  SELECT ERROR_NUMBER() AS E_NO, ERROR_MESSAGE() AS E_MSG;

  END CATCH

  --VERIFY THAT THE CONSTRAINT WORKED

  SELECT

  CC.CC_ID

  ,CC.CC_CODE

  FROM dbo.TBL_CONSTRAINT_COL CC;

  Error message

  E_NO E_MSG

  ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  547 The INSERT statement conflicted with the CHECK constraint "CHKCNST_DBO_CONSTRAINT_COL_CC_CODE". The conflict occurred in database "tempdb", table "dbo.TBL_CONSTRAINT_COL", column 'CC_CODE'.

• kaza_rohan

  SSC-Addicted

  Points: 435

  More actions

  December 28, 2015 at 10:15 pm

  #1848396

  Check:

  Check limits the values that are accepted by one or more columns.

  The Below code accepts only values A or k or R or S, it will not accept other than this.

  USE Rohan

  GO

  CREATE TABLE CHEECK (ID INT, SHORT VARCHAR(2) CHECK (SHORT LIKE '[A,K,R,S]'))

  Thanks,

  Rohan k

  UsefulSite:

  CheckConstraint

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  December 29, 2015 at 8:54 am

  #1848521

  kaza_rohan (12/28/2015)

  ---

  Check:

  Check limits the values that are accepted by one or more columns.

  The Below code accepts only values A or k or R or S, it will not accept other than this.

  USE Rohan

  GO

  CREATE TABLE CHEECK (ID INT, SHORT VARCHAR(2) CHECK (SHORT LIKE '[A,K,R,S]'))

  Thanks,

  Rohan k

  UsefulSite:

  CheckConstraint

  Gosh no. Use a reference table and an FK instead. The reference table will also allow you to add a description to help other programmers know what the values mean instead of relying on either esoteric knowledge or external documentation that might never get updates or might get lost. And for goodness sake, don't use VARCHAR for a 2 character column. That will always add 2 bytes to the column even if the column is storing an empty string with zero length.

  As Eirikur pointed out, the exception might be for simple "Yes/No" requirements but I'd personally never store "Yes/No" in a database. It would either be a BIT datatype or it would be a TINYINT or short CHAR to allow for different "maybe's" and other statuses in the future.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)