none
Appropriate variable type for list of char values while creating table

    Question

  • Hi I am converting MySQL database to SQL server database.

    I need to use a variable with pre-set text values while creating a table.

    In MySQL the following works perfectly:

    CREATE TABLE XYZ (ID varchar(4), TITLE SET ('ENGR I', 'ENGR II', 'ENGR 3'));

    But in SQL server it does not identify the SET as a variable type.

    Any ideas ?

    Monday, February 11, 2019 4:57 PM

All replies

  • You will find many differences between the sql dialects used by each system. You can't do this in tsql - you will need to write separate statements to create the table and then to populate it with a specific set of rows.
    Monday, February 11, 2019 5:24 PM
  • SQL Server does not have a direct replacement for the MySQL SET operation.  

    You can simulate it by doing a check constraint:

    CREATE TABLE [dbo].[XYZ](
    	[ID] [varchar](4) NULL,
    	[TITLE] [varchar](50) NULL
    )
    
    ALTER TABLE [dbo].[XYZ]  WITH CHECK ADD  CONSTRAINT [CK_XYZ_TITLE] CHECK  (([TITLE]='ENGR 3' OR [TITLE]='ENGR II' OR [TITLE]='ENGR I'))
    
    

    Monday, February 11, 2019 6:10 PM
    Answerer
  • Hi jacks4545,

     

    Using CHECK constraints can restrict values inserted or updated. You can create the table with the CHECK constraint.

     

    create table dbo.XYZ(
    ID varchar(4) NOT NULL,
    TITLE varchar(50) CHECK (TITLE='ENGR 3' OR TITLE='ENGR II' OR TITLE='ENGR I')
    )

     

    For more details, please refer to the document: CREATE TABLE (Transact-SQL).

     

    By the way, if you want to supply the specific value when no value is supplied. You can use DEFAULT definitions. Please refer to Using DEFAULT definitions.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, February 12, 2019 6:27 AM