SQL Tutorial - Table Constraints

SQL Tutorial - Table Constraints

• 1.
  © 2013 1keydata.comAll Rights Reserved SQL Tutorial Table Constraints Limiting What Data You Can Put Into A Table
• 2.
  Constraints © 2013 1keydata.comAll Rights Reserved All RDBMS allow for the ability to specify constraints on tables. This ensures data quality by making sure data that does not fit the constraint cannot be entered into the database. This tutorial covers the following constraints: • NOT NULL • DEFAULT • UNIQUE • CHECK
• 3.
  NOT NULL Constraint ©2013 1keydata.com All Rights Reserved Purpose: To ensure that none of the values in a column can be NULL. How specified: In the CREATE TABLE statement, add the word “NOT NULL” after the data type. Example: CREATE TABLE STUDENT_INFO (STUDENT_ID INTEGER, STUDENT_NAME VARCHAR(50) NOT NULL)
• 4.
  DEFAULT Constraint © 20131keydata.com All Rights Reserved Purpose: To specify a default value for a column if no value is specified when data is inserted. How specified: In the CREATE TABLE statement, add the word “DEFAULT” after the data type, followed by the default value. Example: CREATE TABLE EMPLOYEE_SALARY (EMPLOYEE_ID INTEGER, SALARY FLOAT DEFAULT 10000)
• 5.
  DEFAULT Constraint © 20131keydata.com All Rights Reserved INSERT INTO EMPLOYEE_SALARY (EMPLOYEE_ID) VALUES (50); Results in the following row being entered: Even though no SALARY was specified for EMPLOYEE_ID 50, 10000 is entered as it is the default value. EMPLOYEE_ID SALARY 50 10000
• 6.
  UNIQUE Constraint © 20131keydata.com All Rights Reserved Purpose: To ensure that there are no duplicate values in a column. How specified: In the CREATE TABLE statement, add the word “UNIQUE” after the data type. Example: CREATE TABLE STUDENT_INFO (STUDENT_ID INTEGER UNIQUE, STUDENT_NAME VARCHAR(50))
• 7.
  CHECK Constraint © 20131keydata.com All Rights Reserved Purpose: To ensure that data entered into a column satisfies a specific condition. How specified: In the CREATE TABLE statement, add the word “CHECK” after the data type, followed by the condition. Example: CREATE TABLE STUDENT_GRADE (STUDENT_ID INTEGER CHECK (STUDENT_ID > 100), GRADE INTEGER);
• 8.
  CHECK Constraint © 20131keydata.com All Rights Reserved The following SQL, INSERT INTO STUDENT_GRADE (STUDENT_ID, AGRADE) VALUES (10,80); results in an error, since the STUDENT_ID value of 10 does not satisfy the condition that STUDENT_ID must be greater than 100.
• 9.
  1Keydata SQL Tutorial http://www.1keydata.com/sql/sql.html ©2013 1keydata.com All Rights Reserved
• 10.
  1Keydata SQL Tutorial http://www.1keydata.com/sql/sql.html ©2013 1keydata.com All Rights Reserved