1 / 8

SQL constrains and keys

SQL constrains and keys. SORTED RESULTS. Sort the results by a specified criterion SELECT columns FROM tables WHERE predicates ORDER BY column ASC/DESC; select fname, sid from students order by fname ASC;. Constraints. not null validate conditions for fields Unique

fergal
Download Presentation

SQL constrains and keys

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 constrains and keys

  2. SORTED RESULTS • Sort the results by a specified criterion • SELECT columnsFROM tablesWHERE predicatesORDER BY column ASC/DESC; • select fname, sid from students order by fname ASC;

  3. Constraints • not null • validate conditions for fields • Unique create table students ( sid char(5) not null unique, fname varchar(20), lname varchar(20) not null, minit char, age int(3) check (age>0), fatherage int(3) check (fatherage>0), check (fatherage>age)) ;

  4. Exercise • Try to insert at least two different records that would violate at least one constraint.

  5. Primary keys • a primary key is a value that can be used to identify a particular row in a table. • A super key for a relation scheme R is any subset k of R that satisfies the property that in any valid relation under the scheme R it is not possible to have two different tuples with the same values under k. • A candidate key for R is any super key for R such as none of its proper subsets is also a super key • A primary key is one of the candidate keys chosen by the designer of the database

  6. Primary key examples • What would happen if more than one bank account would have the same number ? • Solution : unique ids ? • How to enforce: primary keys • Why not only “unique” : • primary keys are automatically indexed by the dbms • primary keys are not null • if a column is unique than more than one null row with data can exist create table students ( sid varchar(5), fname varchar(20), lname varchar(20) not null, minit char, primary key (sid));

  7. The primary key may be a tuple create table enrolls ( sid varchar(5), term varchar(10), lineno int(4), primary key (sid,term,lineno));

  8. Foreign keys • A foreign key is a field or group of fields in a database record that point to a key field or group of fields forming a key of another database record in some (usually different) table. Usually a foreign key in one table refers to the primary key of another table create table enrolls ( sid varchar(5) not null, term varchar(10), lineno int(4), primary key (sid,term,lineno), foreign key (sid) references students);

More Related