CODEWITHSUNDEEP
sqlsql-serveroracle-databasedatabase-designconstraints
james
james

Reputation: 35

Help with a constraint

I'm creating a table and one of the constraints is that first character must be "G" and then followed by 5 numeric digits:

CREATE TABLE PHONE (
  PHONEID CHAR (6) NOT NULL,
  PHONENO NUMERIC NOT NULL,
  CONSTRAINT PHONE_PHONEID_PK PRIMARY KEY (PHONEID),
  CONSTRAINT PHONE_PHONENO_UK UNIQUE (PHONENO)
);

How do I do this using sql? I need to do it to phonied

Upvotes: 2

Views: 802

Answers (2)

King Skippus
King Skippus

Reputation: 3826

In Oracle, I think it would be:

CREATE TABLE PHONE (
  PHONEID CHAR (6) NOT NULL,
  PHONENO NUMERIC NOT NULL,
  CONSTRAINT PHONE_PHONEID_PK PRIMARY KEY (PHONEID),
  CONSTRAINT PHONE_PHONENO_UK UNIQUE (PHONENO),
  CONSTRAINT PHONE_PHONEID_CK CHECK(REGEX_LIKE(PHONEID, '^G\d{5}$'))
);

*Disclaimer: It's been a while since I've worked with Oracle, I don't have access now to an Oracle database to validate the above, and this is based on the documentation of Check Restraints and the REGEXP_LIKE function. Some tweakage might be necessary.

Upvotes: 1

Alex Aza
Alex Aza

Reputation: 78467

In MS SQL this would look like this:

create table Phone
(
    PhoneId char(6) not null
        constraint Phone_PhoneId_PK primary key
        constraint Phone_PhoneId_CK check (PhoneId like 'G[0-9][0-9][0-9][0-9][0-9]'),
    PhoneNumber numeric not null
        constraint Phone_PhoneNumber_UK unique
)

insert Phone
values('G00001', 123) -- pass

insert Phone
values('G0001', 123)  -- fail

insert Phone
values('D0001', 123) -- fail

Upvotes: 2

Related Questions