locked
Working with Data Validation and Constraints RRS feed

  • Question

  • I am creating my first T-SQL statements, so I am new to this.
    I created a this table below:

    CREATE TABLE myMusicCollection(
    ARTIST_FNAME VARCHAR(25) NOT NULL,
    ARTIST_LNAME VARCHAR(25) NOT NULL,
     ALBUM_TITLE VARCHAR(100) NOT NULL,
        GENRE VARCHAR(50) NOT NULL,
    RELEASE_DATE VARCHAR(100) NOT NULL,
     VOL_NUMBER INT NOT NULL,
           PRIMARY KEY (VOL_NUMBER) 
    );


    My question is, how do I place a validation or some kind of a constraint on the Column VOL_Number which is also the Primary Key, what I want to do is make this field limited to only 6 digits. I could use CHAR but I want this to be the primary key.

    Thanks everyone!

    Synthologic

    Sunday, August 28, 2011 8:57 AM

Answers

  • My question is, how do I place a validation or some kind of a constraint on the Column VOL_Number which is also the Primary Key, what I want to do is make this field limited to only 6 digits. I could use CHAR but I want this to be the primary key.

    A CHAR(6) column can be the primary key.  If you want to use a CHAR(6) data type and accept exactly 6 numeric digits, add a CHECK constraint:

    CREATE TABLE myMusicCollection(
    ARTIST_FNAME VARCHAR(25) NOT NULL,
    ARTIST_LNAME VARCHAR(25) NOT NULL,
     ALBUM_TITLE VARCHAR(100) NOT NULL,
      GENRE VARCHAR(50) NOT NULL,
    RELEASE_DATE VARCHAR(100) NOT NULL,
     VOL_NUMBER INT NOT NULL
    	CONSTRAINT CK_myMusicCollection_VOL_NUMBER CHECK
    	(VOL_NUMBER LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
        PRIMARY KEY (VOL_NUMBER) 
    );
    
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Synthologic Sunday, August 28, 2011 9:48 PM
    Sunday, August 28, 2011 8:23 PM

All replies

  • If you declare as INT, it can get maximum value of 2147483647. So, there is 10 digits even smaller then maximum 10 digit value (9999999999). What I know, there is no way out to restrict any integer type to some digits. However, for convenience, SqlServer developers kept few versions of Integer type: BigInt, Int, SmallInt and TinyInt. Read more about their specifications:

    http://msdn.microsoft.com/en-us/library/ms187745.aspx

    Regards.


    Sunday, August 28, 2011 9:07 AM
  • create table tableA (
     colA int not null primary key,
     constraint chk_tableA_colA
     check (case when len(colA)>6 then 0
                        else 1 end = 1)
     )
    insert into tableA values (1)
    insert into tableA values (12)
    insert into tableA values (123)
    insert into tableA values (1234)
    insert into tableA values (12345)
    insert into tableA values (123456)
    insert into tableA values (1234567)--failed

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, August 28, 2011 9:08 AM
    Answerer
  • >> My question is, how do I place a validation or some kind of a constraint on the column vol_number which is also the PRIMARY KEY, what I want to do is make this field [sic: columns are not fields] limited to only 6 digits. <<

    Why not trying be a good programmer and a bad amateur? You can download industry standard identifiers for music. Shwann catalog? ISAN? RIAA? In fact, we have a local TV commercial for a service that builds a complete music database for you. It even adds the album cover art. 

    We have a DATE data type now, too. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Sunday, August 28, 2011 4:15 PM
  • >> My question is, how do I place a validation or some kind of a constraint on the column vol_number which is also the PRIMARY KEY, what I want to do is make this field [sic: columns are not fields] limited to only 6 digits. <<

    Why not trying be a good programmer and a bad amateur? You can download industry standard identifiers for music. Shwann catalog? ISAN? RIAA? In fact, we have a local TV commercial for a service that builds a complete music database for you. It even adds the album cover art. 

    We have a DATE data type now, too. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    I am not sure how to respond to your answer AKA insults. I am not trying to build a database for music, this is a practice session, I had to use a scenario for an example. I could have used Animals or Automobiles. It was simply a question, you did not have to answer.

    Synth

    Sunday, August 28, 2011 6:12 PM
  • If you declare as INT, it can get maximum value of 2147483647. So, there is 10 digits even smaller then maximum 10 digit value (9999999999). What I know, there is no way out to restrict any integer type to some digits. However, for convenience, SqlServer developers kept few versions of Integer type: BigInt, Int, SmallInt and TinyInt. Read more about their specifications:

    http://msdn.microsoft.com/en-us/library/ms187745.aspx

    Regards.



    Thanks for your input!
    Sunday, August 28, 2011 6:14 PM
  • create table tableA (
     colA int not null primary key,
     constraint chk_tableA_colA
     check (case when len(colA)>6 then 0
                        else 1 end = 1)
     )
    insert into tableA values (1)
    insert into tableA values (12)
    insert into tableA values (123)
    insert into tableA values (1234)
    insert into tableA values (12345)
    insert into tableA values (123456)
    insert into tableA values (1234567)--failed

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     

    Thanks for your input.

     

    Sunday, August 28, 2011 6:16 PM
  • You can easily restrict the value in any column with check constraints.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, August 28, 2011 6:53 PM
  • My question is, how do I place a validation or some kind of a constraint on the Column VOL_Number which is also the Primary Key, what I want to do is make this field limited to only 6 digits. I could use CHAR but I want this to be the primary key.

    A CHAR(6) column can be the primary key.  If you want to use a CHAR(6) data type and accept exactly 6 numeric digits, add a CHECK constraint:

    CREATE TABLE myMusicCollection(
    ARTIST_FNAME VARCHAR(25) NOT NULL,
    ARTIST_LNAME VARCHAR(25) NOT NULL,
     ALBUM_TITLE VARCHAR(100) NOT NULL,
      GENRE VARCHAR(50) NOT NULL,
    RELEASE_DATE VARCHAR(100) NOT NULL,
     VOL_NUMBER INT NOT NULL
    	CONSTRAINT CK_myMusicCollection_VOL_NUMBER CHECK
    	(VOL_NUMBER LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
        PRIMARY KEY (VOL_NUMBER) 
    );
    
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Synthologic Sunday, August 28, 2011 9:48 PM
    Sunday, August 28, 2011 8:23 PM