[SQL] 1. SSMS - created a table with IDENTITY(0,1) on the id, after inserting values id set to...

Using Microsoft SQL Server Management Studio

1. Wanted emp_id to auto_increment like 1,2,3,4...
2. Wanted first_name and last_name to only allow inputs whiches first letter is uppercase.

Created a table with:

CREATE TABLE employee(
emp_id INT PRIMARY KEY IDENTITY(0,1) NOT NULL,
first_name VARCHAR(20) CHECK(first_name LIKE UPPER(LEFT(first_name,1))) NOT NULL,
last_name VARCHAR(40) CHECK(last_name LIKE UPPER(LEFT(last_name,1))) NOT NULL,
birth_date DATE NOT NULL,
sex char(1) CHECK(sex IN ('M','F')) NOT NULL,
salary MONEY NOT NULL,
super_id INT DEFAULT NULL,
branch_id INT DEFAULT NULL
);


Inserted first round of values with:

INSERT INTO employee(first_name, last_name, birth_date, sex, salary)
VALUES ('David', 'Wallace', '1967-11-17', 'M', 250000);

1. After inserting values, emp_id got set to 5, I intended it to be 1.
2. first_name VARCHAR(20) CHECK(first_name LIKE UPPER(LEFT(first_name,1))) NOT NULL, and last_name VARCHAR(40) CHECK(last_name LIKE UPPER(LEFT(last_name,1))) NOT NULL, are my attempts of assuring the first letter of first_name and last_name are uppercase. Instead, these columns ONLY allow the input to be 1 uppercase letter, tested that by changing ('David', 'Wallace', '1967-11-17', 'M', 250000); to ('D', 'W', '1967-11-17', 'M', 250000);. Any help?

Continue reading...