SQL Table Constraints

Creating constraints with SQL create table

Constraints types

  • We use Constraints to provide important data integrity protection on the table-fields and / or table relationships to other tables
  • Most RDBMS have included in the CREATE TABLE or ALTER TABLE statement options to specify constraints types of PRIMARY KEY, UNIQUE, DEFAULT, CHECK, REFERENCES, and FOREIGN KEY
    • PRIMARY KEY marks the columns (that do not allow nulls) as the primary key of the table. Combination of values for the columns included in the PRIMARY KEY must be unique, otherwise the values will be rejected.
    • UNIQUE also provide warranty on inequality between the values in a column, but allows that the column can be NULL.
    • DEFAULT defines a value system automatically delivers when the user enters data, and does not provide an explicit.
    • CHECK specifies what data may be entered in a particular column. It is sometimes called rules or validation rules (NOT in MySQL – must use triggers).
    • REFERENCES and FOREIGN KEY defines the bond between a foreign key and primary key in another table. You can enter values in columns defined with REFERENCES, but it requires that these exist in the columns to the table referenced, otherwise the entry will be rejected.
  • Constraints can be given on the column (field) level or table level. Some RDBMS have a solution for both alternatives, but I think you may find most of the opportunities on the table level.
  • Syntax for constraints can also varies from RDBMS to RDBMS, so check your system manuals for specifics.
  • Typically, the syntax is similar as shown in the following, with column-level constraints, or alternatively after each column definition.
  • The constraint clauses will always include constraint keywords like DEFAULT, CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY and/or REFERENCES.
  • In most cases you must use the word CONSTRAINT and a name for the constraint.

Creating Tables With Constraints

Syntax:
CREATE TABLE table_name
(column_name datatype [NULL | NOT NULL] [DEFAULT default_value]
 [column_constraint_clause]...
 [, column_name datatype [NULL | NOT NULL][DEFAULT default_value]
 [column_constraint_clause]...]...
 [, table_constraint_clause]...)
Example with Column level constraints:
MySQL, Oracle, SQL server, PostgreSQL:
CREATE TABLE ADDRESSES2 (
 ADDRESS_ID int NOT NULL PRIMARY KEY,
 ADDRESS_TYPE varchar(10) DEFAULT 'Unknown'
     CHECK (ADDRESS_TYPE IN ('Unknown','Home','Work','Cell')),
 CUSTOMER_ID int NOT NULL REFERENCES CUSTOMER (CUSTOMER_ID),
 ADDR_LINE varchar(100) ,
 CITY varchar(100) ,
 US_STATE_CD char(2) NOT NULL REFERENCES US_STATES (US_STATE_CD),
 ZIP char(5)
)
  • MySQL will ignore CHECK. Use Trigger instead.
  • Oracle, PostgreSQL and SQL server require existence of the tables CUSTOMER and US_STATES.
Example with Table level constraints:
MySQL, Oracle, SQL server, PostgreSQL:
CREATE TABLE ADDRESSES2 (
 ADDRESS_ID int NOT NULL,
 ADDRESS_TYPE varchar(10) DEFAULT 'Unknown',
 CUSTOMER_ID int NOT NULL ,
 ADDR_LINE varchar(100) ,
 CITY varchar(100) ,
 US_STATE_CD char(2) NOT NULL ,
 ZIP char(5),
 PRIMARY KEY (ADDRESS_ID),
 CONSTRAINT CC CHECK (ADDRESS_TYPE IN ('Unknown','Home','Work','Cell')),
 CONSTRAINT FK_CUST FOREIGN KEY (CUSTOMER_ID) REFERENCES  CUSTOMER (CUSTOMER_ID),
 CONSTRAINT FK_STAT FOREIGN KEY  (US_STATE_CD) REFERENCES  US_STATES (US_STATE_CD)
)
  • Oracle, PostgreSQL and SQL server require existence of the tables CUSTOMER and US_STATES.
  • DEFAULT value is normally only a column specification type.
  • The CC constraint is a checker for acceptable values for the column, ADDRESS_TYPE (ignored by MySQL).
  • The foreign key, FK_CUST, is a constraint that secures reference to the column, CUSTOMER_ID, in the table, CUSTOMER. (Values for the column, CUSTOMER_ID, in the table, ADDRESSES2, must exist in the column, CUSTOMER_ID, in the table, CUSTOMER.)
  • The foreign key, FK_STAT, is a constraint that secures reference to the column, US_STATE_CD, in the table, US_STATES. (Values for the column, US_STATE_CD, in the table, ADDRESSES2, must exist in the column, US_STATE_CD, in the table, US_STATES.)
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.