MS Sql Server: Customizing Your Data Base Design
2 likes525 views
This document discusses various ways to customize databases in SQL Server, including managing data integrity through primary keys, foreign keys, and constraints. Primary keys uniquely identify records, can be a single field or multiple fields, and can be added to tables during or after creation. Foreign keys reference primary keys in other tables and have similar rules for addition. Not null, unique, and check constraints enforce data rules and prevent invalid entries. Customizations help optimize efficiency and control over underlying data.
More Related Content
What's hot (16)
Viewers also liked (20)
Similar to MS Sql Server: Customizing Your Data Base Design (20)
More from DataminingTools Inc (20)
Recently uploaded (20)
MS Sql Server: Customizing Your Data Base Design
- 1. 7SQL SERVER: CUSTOMIZINGTHE DATABASE DESIGN
- 2. Customizing a databaseThe Power of a DBMS lies in the control that it offers the user over the data lying underneath. By customizing the structure of the data, a programmer can optimize the efficiency of the system in a specific implementation.A Basic customization feature is controlling the type of data that is stored in a database table.Some of the such basic customizations are: Adding a primary key
- 3. Adding a foreign key
- 4. Adding Constraints
- 5. Unique Constraint
- 6. Not Null Constraint
- 7. Check ConstraintManaging Data IntegrityData integrity controls of the SQL Server 2008, help to guarantee the accuracy, validity, and correctness of data stored. They are a set of rules which can be defined by the programmer, so as to decide if a given data is valid for the system or not.Now, let us see more about them.
- 8. Primary KeysWhat is a Primary Key:A Primary key is a field/attribute in a table which is used to uniquely identify a recordEg: Consider a dream databaseHere, a particular record can be uniquely located using the DreamNumber and hence, it is taken as the Primary key.If there are more than fields, eligible of being the primary key, the decision of choosing one among them lies with the DB designer
- 9. Primary KeyAdding a Primary Key:A Table must have only one primary key. The Primary key must be defined during the creation of the table. Syntax:Create table <tableName> ( <fieldName1><field1Type> primary key, ..);Example: Consider the creation of the following table. The field ‘Dream Number’ is to be designated as the primary keyCreate table dreamtable( dreamnumberintprimary key, dream varchar(10), dreamdate date, dreamtime time, dreamtypevarchar(10)) ;
- 10. Redefining primary keyNow, let us take a step back and redefine the primary key that we have learnt already.The definition that we learnt was:A Primary key is a field which lets us to uniquely identify a record in a table.This definition may sound intuitive, but in real word cases, a single field may not be enough to identify a record in a table. In such cases, a collection (two or more) of fields are used as a primary key. Consider the following example of a hospital’s patient table:
- 11. Redefining primary keyIn this case, it would be possible to locate a record uniquely only with information of more than one field values. Example:PatientID, Date of Admisssion and Time of Admission can be combined to form a primary key. The SQL Command to do this is via a constraint:create table <tableName> (<field names>,…, constraint <constraintName> primarykey(<filedName1>,<fieldName2>,..) );Example: create table Patients(patientidvarchar(10), patientnamevarchar(20), dateofadmission date, timeofadmission time, issue varchar(20),constraint patientpkeysprimary key(patientid, dateofadmission, timeofadmission) );
- 12. Adding primary keys to existing tablesThe SQL Server allows primary keys to be added to an existing table. Syntax:ALTER TABLE <tableName> ADD CONSTRAINT <constraintName> PRIMARY KEY (<FieldName>);Example: Consider the patient database. The command to define the primary key after creating the table is:Alter table Patients add constraint patientpkeysprimary key(patientid, dateofadmission, timeofadmission) );
- 13. Foreign KeysForeign Key:A Foreign key is a field/attribute in one table which is used as a primary key in another tableEg: Consider a dream databaseEvery foreign key value must be present as a primary key in the referenced table.Eg: A dream number ‘3’ isn’t possible in ‘Luck Table’ unless such a dream number exists in the ‘Dream Table’Dream TableNotice that foreign key entries can repeat (where-as primary key entries can’t!)Foreign KeyPrimary KeyLuck TableRefer-ences
- 14. Foreign KeysIn SQL Server, a foreign key is created as follows:Create table <Table2_Name> (<filedName1> foreign key references <Table1_Name> (Table1FieldName));In the example that we just considered, the dreamNumberof the Luck Table is using the value of the primary Key dreamNumberfrom Dream Table. Hence, it is a foreign Key. The SQL statement to effect this is:Create table lucktable (dreamnumberintforeign key references dream(dreamnumber), luck varchar(15), predictor varchar(20));NOTE: It is essential to create the table which is being referenced by the foreign key before creating the foreign key itself.
- 15. Adding foreign keys to existing tablesThe SQL Server allows foreign keys to be added to an existing table. Syntax:ALTER TABLE <tableName> ADD FOREIGN KEY (<fieldName>) REFERENCES <referencedTable>(<referencedFieldName>);Example: Table B has an attribute B1. B1 references to the primary key A1 of table A. Now, the command to add B1 as a foreign key of B is as follows:Table APrimary Key of ATable BField A1Foreign Key of BField B1<< B1 references A1 Alter table B add foreign key(B1) references A(A1) ;
- 16. Not Null ConstraintIn feeding data into a database, it lies with the user to feed data for a field or assign a null value. But sometimes, the value of the field may be highly valuable for data processing. In such places, we may prevent a ‘null’ from being assigned to a field by using the not null constraint. Eg: Consider a customer table. Here, the name of the customer is very important. So, it can be made to be not null.Note: CutomerID being a primary key, inherently, can never be null.Create table customer(customeridint primary key, name varchar(10) not null, address varchar(30));An attempt to insert a null value into a not-null field, will be flagged as an error.
- 17. Unique ConstraintIn SQL Server, it is possible to designate a field to be unique, .i.e., the field will not accept duplicate values. Primary keys are inherently unique in nature.Eg: Consider a customer table. Here, the credit card number of the customers will be unique.Create table customer(customeridint primary key, name varchar(10) not null, creditcardnumbervarchar(20) unique);An attempt to insert a credit card number that already exists in the table will be flagged as an error.
- 18. Check ConstraintA Check constraint is used to specify some rules for the data that can be stored for a field.A Check constraint can be defined while creating a table (with create table) or can be added to an existing table.While adding a check constraint to an existing table, the data that the table holds is checked against the constraint being defined. To prevent this checking, the nocheck command can be used.A Check constraint can be defined on a single column(field) or across multiple columns of the same table. fieldname can take values between 1 and 100. eg: 1,2,3,…,99,100SQL Syntax:Create table <tableName> (<fieldname> check (fieldname > 100) );Create table <tableName> (<fieldname> check (fieldname between 1 and 100) );Create table <tableName> (<fieldname> check (fieldname like ‘[0-9][0-9][a-z][A-Z]’) );[0-9]: Denotes a single value in the range specified
- 19. Adding Check Constraint to an existing tableIt is possible to add check constraints to existing tables using the alter table command:Syntax:ALTER TABLE <tableName> ADD CHECK (<fieldName> <condition>);Example:ALTER TABLE student ADD CHECK (cgpa Between 0 and 10 );
- 20. Summary7. Customizing your database:Managing Data integrity
- 21. Primary Keys
- 22. Definition
- 23. Adding primary key while creating table
- 24. Adding primary key to existing tables
- 25. Multiple field – primary key
- 26. Foreign Keys
- 27. Definition
- 28. Adding foreign key while creating table
- 29. Adding foreign key to existing table
- 32. Check ConstraintVisit more self help tutorialsPick a tutorial of your choice and browse through it at your own pace.The tutorials section is free, self-guiding and will not involve any additional support.Visit us at www.dataminingtools.net