Download
sql ddl constraints n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL DDL constraints PowerPoint Presentation
Download Presentation
SQL DDL constraints

SQL DDL constraints

192 Vues Download Presentation
Télécharger la présentation

SQL DDL constraints

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. SQL DDL constraints Restrictions on the columns and tables SQL DDL Constraints

  2. Different types of constraints • Entity constraints • PRIMARY KEY • UNIQUE • Domain constraints • CHECK constraints • CHECK salary > 0 • DEFAULT value • Referential integrity constraints • FOREIGN KEY • Column level • Constrains applies to a column • Table level • Constrains applies to a table SQL DDL Constraints

  3. Checking constraints • Constraints are automatically checked by the DBMS every time you try to do • INSERT, • UPDATE • or DELETE SQL DDL Constraints

  4. 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

  5. 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

  6. 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

  7. DEFAULT constraints • Default values • Used in INSERT statements when no value is supplied • Example • enrollmentDate data DEFAULT getDate() SQL DDL Constraints

  8. Check constraintsSimple 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

  9. 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

  10. 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

  11. Ignoring existing datawhen 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

  12. Disabling and enabling constraints checking • ALTER TABLE … NOCHECK CONSTRAINT constraintName • Checking is disabled • ALTER TABLE … CHECK CONSTRAINT constraintName • Checking is enabled SQL DDL Constraints