1 / 12

SQL DDL constraints

SQL DDL constraints. Restrictions on the columns and tables. 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

teigra
Download Presentation

SQL DDL constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related