CODEWITHSUNDEEP
sql-server
user3376708
user3376708

Reputation:

What does the constraint mean?

I am new to SQL and I am trying to create a table with a constraint but I have not used the constrant before and I am not actually certain what this constraint does uc_ID on PId, LastName?

I want to create a constraint that will only allow alpha numeric values in a column?

Code:

CREATE TABLE Persons
 (
 PId   int   identity(1,1)       NOT NULL,
 LastName           varchar(25)  NOT NULL,
 FirstName          varchar(25)  NOT NULL,
 Address1           varchar(25)  NOT NULL,
 City               varchar(25) NOT NULL
 CONSTRAINT uc_ID UNIQUE (PId,LastName)
 )

CREATE TABLE E
 (
 PId            int identity(1,1) NOT NULL,
 LastName           varchar (25)  NOT NULL,
 FirstName          varchar (25)  NOT NULL,
 Address1           varchar (25)  NOT NULL,
 City               varchar (25)  NOT NULL
 CONSTRAINT OnlyAlphanumeric CHECK ([FirstName] NOT LIKE '%[^A-Z0-9]%')
 )

Another Example (is not):

CREATE TABLE EEE
 (
 PId            int identity(1,1) NOT NULL,
 FirstName          varchar (50)  NOT NULL,
 CONSTRAINT CHECK ([FirstName]    LIKE '%[A-Za-z]%')
 )

Upvotes: 2

Views: 259

Answers (3)

Marcel N.
Marcel N.

Reputation: 13976

If you want a constraint that allows only alphanumeric characters in a column you can use this (for example for FirstName:

ALTER TABLE [Persons] ADD CONSTRAINT OnlyAlphanumeric CHECK ([FirstName] NOT LIKE '%[^A-Z0-9 ]%')

Disclaimer: taken from here (and tested).

If you want the constraint to be added when the table is created:

CREATE TABLE Persons
 (
 PId   int   identity(1,1)       NOT NULL,
 LastName           varchar(25)  NOT NULL,
 FirstName          varchar(25)  NOT NULL,
 Address1           varchar(25)  NOT NULL,
 City               varchar(25) NOT NULL
 CONSTRAINT OnlyAlphanumeric CHECK ([FirstName] NOT LIKE '%[^A-Z0-9 ]%')
 )

Note that constraint names are unique per database.

Alternatively, you can create an AFTER INSERT and AFTER UPDATE trigger to make this validation, but a constraint works just fine in this case.

Upvotes: 0

New Vision
New Vision

Reputation: 31

Try:

ALTER TABLE TableName ADD CONSTRAINT Only_Characters_And_Numbers CHECK ColumnName NOT LIKE '%[^A-Z0-9 ]%'

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

Means that the combination of Pid + LastName must be unique.
Since Pid is an identity, in normal circumstances it cannot be duplicated, so that constraint seems somehow redudant.

Upvotes: 1

Related Questions