CODEWITHSUNDEEP
sqlsql-serversqldatatypes
vipul
vipul

Reputation: 89

Understanding user-defined data types in SQL

i am trying to understand the architecture of PUBS Database sample by Microsoft In there, I am looking at au_id Column, who has user-defined datatype id:varchar(11).

So, if I understand clearly, varchar(11) means it allows to enter 11 characters in the cell. But if I enter

Why does this happen ? Do they have some method to validate this entry. I can only find a user-defined datatype called id in the User-Defined Data Type Folder

Thank you in advance.

Upvotes: 0

Views: 798

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Okay, just found the script that will create the pubs database.

The au_id column on authors is defined as:

CREATE TABLE authors
(
   au_id          id
     CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
     CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED,
  /* More columns */

It's the CHECK constraint that's rejecting your invalid values, rather than anything connected with the user-defined type. If you examine the error messages, it probably mentions that it's a CHECK constraint that's failing.

(BTW - I'd assumed that this was SSN format, not telephone numbers - anyone confirm?)

User defined types in SQL Server (other than table types) don't offer much value - all they really do is associated a shorthand name for a built-in type with all scale/precision/length options fixed.

They would be tremendously useful if the system would let you set up strict types - such that two values of the same underlying type, but with different type names, are not comparable/assignable - you'd get far better warnings/errors rather than queries proceding with mis-aligned joins, for example.

Upvotes: 4

Related Questions