CODEWITHSUNDEEP
mysql
user2975266
user2975266

Reputation: 67

Validation rule in mysql?

I have created a database for a computer inventory. My assignment says that I can only allow certain words to be entered into a field such as "Dell" and "IBM". Here's how I created the table: 

Create table computer_inventory ( 
 assetnumber int(10) not null default 0,
 manufacturer char(3) ENUM('Dell', 'IBM', 'OtherCompany') NOT NULL default ' ', 
 originalcost decimal(12,2) not null default 0, 
 currentvalue decimal(12,2) not null default 0, 
 boughtfrom varchar(20) not null default ' ', 
 instock tinyint(1) not null default 0, 
 currentuser varchar(20) not null default ' ',
 userphonenum varchar(13) not null default ' ',
 boughtdate datetime not null default '0000-00-00'
);

Now I am suppose to make it where a person can ONLY enter in "DELL" or "IBM" for manufacturer.

Upvotes: 1

Views: 3344

Answers (2)

Geery.S
Geery.S

Reputation: 121

Two errors are in your code :

  1. in line three is not allow : char(3) because, you have declared values with more chars ( dell - 4 chars)
  2. in line three is not allow : default ' ' - because in your declared list of values isn't ' '. There can be only values from list ENUM('Dell', 'IBM', 'OtherCompany')

the right code : 

Create table computer_inventory ( 
assetnumber int(10) not null default 0,
manufacturer ENUM('Dell', 'IBM', 'OtherCompany') NOT NULL, 
originalcost decimal(12,2) not null default 0, 
currentvalue decimal(12,2) not null default 0, 
boughtfrom varchar(20) not null default ' ', 
instock tinyint(1) not null default 0, 
currentuser varchar(20) not null default ' ',
userphonenum varchar(13) not null default ' ',
boughtdate datetime not null default '0000-00-00')

Upvotes: 1

Andy
Andy

Reputation: 50540

Take a look at the ENUM type for this particular use case.

This type of field allows you to explicitly state what is allowed in this column. For your case, you'd want 'Dell' and 'IBM' and any other company you want to allow.

CREATE TABLE tablename (
    company ENUM('Dell', 'IBM', 'OtherCompany')
);

This command will create a table (tablename) with a single field (company). In that field, only three values will be allowed. 'Dell', 'IBM' and 'OtherCompany'.

Edit: Based on your edit, you can modify your manufacturer line to read as above.

manufacturer ENUM('Dell', 'IBM', 'OtherCompany') NOT NULL

One thing to note from the documentation:

If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.

In this case, that means if you don't pass a manufacturer, it will default to Dell

Upvotes: 2

Related Questions