none
How can I add a minimum length of characters in a varchar? RRS feed

  • Question

  • I have a table:

    create table "Users"
    (
    Email VARCHAR(320) COLLATE SQL_Latin1_General_CP1_CI_AS Primary key,
    Psw varchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS not null
    );

    I want to set a limitations to both "Email" and "Psw" to limit their minimum acceptable length to some amount. (ex: 5, 3) I saw a lot of solutions, but can be applied when the table is created. (but later, by altering the table) 

    How can apply the limitation when the table is created, and not later? 

    Friday, November 1, 2019 12:41 PM

Answers

  • CREATE TABLE Users (
    	Email varchar(320) CONSTRAINT CK_Users_Email CHECK (LEN(Email) >= 5),
    	Psw varchar(32) CONSTRAINT CK_Users_Psw CHECK (LEN(Psw) >= 3) NOT NULL,
    	CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED (Email ASC)
    );
    GO


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by avivgood Friday, November 1, 2019 2:40 PM
    Friday, November 1, 2019 2:34 PM

All replies

  • Add a check constraint on the column:

    ALTER TABLE Users
    ADD CONSTRAINT CK_Users_Email CHECK (LEN(Email) >= 5);
    
    ALTER TABLE Users
    ADD CONSTRAINT CK_Users_Psw CHECK (LEN(Psw) >= 3);


    A Fan of SSIS, SSRS and SSAS

    Friday, November 1, 2019 2:08 PM
  • Add a check constraint on the column:

    ALTER TABLE Users
    ADD CONSTRAINT CK_Users_Email CHECK (LEN(Email) >= 5);
    
    ALTER TABLE Users
    ADD CONSTRAINT CK_Users_Psw CHECK (LEN(Psw) >= 3);


    A Fan of SSIS, SSRS and SSAS

    But I'm trying to achieve the limitation in the create statement itself, without an external quary
    Friday, November 1, 2019 2:13 PM
  • CREATE TABLE Users (
    	Email varchar(320) CONSTRAINT CK_Users_Email CHECK (LEN(Email) >= 5),
    	Psw varchar(32) CONSTRAINT CK_Users_Psw CHECK (LEN(Psw) >= 3) NOT NULL,
    	CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED (Email ASC)
    );
    GO


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by avivgood Friday, November 1, 2019 2:40 PM
    Friday, November 1, 2019 2:34 PM