Transcript SQL DDL constraints

SQL DDL constraints
Restrictions on the columns and
tables
SQL DDL Constraints
1
Different types of constraints
• Entity constraints
– PRIMARY KEY
– UNIQUE
• Domain constraints
– CHECK constraints
• CHECK salary > 0
• Column level
– Constrains applies to a
column
• Table level
– Constrains applies to a
table
– DEFAULT value
• Referential integrity
constraints
– FOREIGN KEY
SQL DDL Constraints
2
Checking constraints
• Constraints are automatically checked by the
DBMS every time you try to do
– INSERT,
– UPDATE
– or DELETE
SQL DDL Constraints
3
Constraints should have a name
• Constraints (like other database objects) have names.
• Names are use when constraints are created, altered or
dropped.
• Example names
– PK_Student_ID
• Primary key in the Student table, is ID
• Default names
– SQL Server will generate default names for your constrains
• Which can be quite unreadable
– You might be better of naming the constraints your self
SQL DDL Constraints
4
Primary key constraint
• Every table should have a primary key.
• A primary key is a set of attributes
– Often the set has only 1 element
• The values of the primary key attributes must be
unique.
• Primary key attributes must be NOT NULL
• Primary keys should generally be ID
– Don’t use real data as a primary key
– Data type INT. Generated using IDENTITY(1,1)
• Syntax
– Attrib dataType IDENTITY(1,1) PRIMARY KEY
SQL DDL Constraints
5
UNIQUE constrains
• Sometimes a table has more candidate keys
– One candidate is selected PRIMARY KEY
– Others are declared UNIQUE
• Syntax
– Attrib DataType UNIQUE [NOT NULL]
– Unlike PRIMARY KEY a UNIQUE attribute can
accept NULL
SQL DDL Constraints
6
DEFAULT constraints
• Default values
• Used in INSERT statements when no value is
supplied
• Example
– enrollmentDate data DEFAULT getDate()
SQL DDL Constraints
7
Check constraints
Simple business rules
• Examples
– Salary int CHECK (salary > 0)
– Month tinyInt CHECK (month BETWEEN 1 AND 12)
– Day varchar(10) CHECK (day IN (’Monday’,
’Tuesday’, et.)
SQL DDL Constraints
8
Foreign key constraints
• Dependency between two tables
– Referring table
• Has the foreign key
– Referenced table
• The foreign key reefers to the primary key of this table
– Recursive relationship / self referencing table
• The referring table and the referenced table is the same
• Examples
– Employee has a supervisor / boss, who is another Employee
– Category has a super category
• Syntax
– Attrib dataType FOREIGN KEY REFERENCES tableName
(attributName)
SQL DDL Constraints
9
Cascading actions
• Generally you cannot update / delete referred
rows, but …
• Syntax
– CONSTRAINT someName FOREIGN KEY REFERENCES
sometable(someattribute_s) ON UPDATE
someActionA ON DELETE someActionB
– someAction can be
•
•
•
•
No action: default
Cascade: referring rows are updated / deleted
Set null: referring values are set to null
Set default: referring value are set to their default value
SQL DDL Constraints
10
Ignoring existing data
when you create a constraint
• Adding a constraint to an existing table can be
a problem
– If the table has data that does NOT conform with
the constraint
– It is possible to add a constraint WITHOUT
checking the existing rows
• ALTER TABLE … WITH NOCHECK ADD CONSTRAINT …
SQL DDL Constraints
11
Disabling and enabling constraints
checking
• ALTER TABLE … NOCHECK CONSTRAINT
constraintName
– Checking is disabled
• ALTER TABLE … CHECK CONSTRAINT
constraintName
– Checking is enabled
SQL DDL Constraints
12