designing table in SQL

Question

Hi Friends I want to know that during designing a table how can I create one to one and one to many relationship in SQL ?

Nattudurai Eswaramurthy · Answer

One to One relationShip : Any time two tables have the same primary key and are joined by a foreign key  a one-to-one relationship exists. The first two examples below use this diagram: Scenario 1) A subset of fields. Consider that a system contains people and not all them are users. All users are people and could duplicate the fields of [person]. Rather than repeat these data in table [user] creating [user] as a subset of [person] saves disk space by utilizing inheritance. By not having the fields of [user] in [person] all fields in [person] have meaning to its table: normalization. Any updates to personal information can be done with the same interface for [person] and [user] without any duplication of personal data. Scenario 2) Enforcing permissions based on data within the system. Separate from logins and database users systems often have users stored in the database as data records. Organized as above the table [user] ensures that any records therein have a certain level of ability with the system and associating permissions or roles can be isolated to only users. Scenario 3) Organizing tables' fields. SQL Server does not have an easy way to reorder a table's columns and if a table that can have new fields from multiple logical business reasons they are likely to introduce new columns at different speeds. It is possible to create a one-to-one relationship to isolate which department requested which columns or simply to organize them for better readability of metadata. The potential cost is an extra join in the queries which use the table but a view which sews them together solves that. An example implementation could be as below a way to separate business additions from the technical mechanics. Example : CREATE TABLE Organization ( ID int PRIMARY KEY  Name varchar ( 200 )  Address varchar ( 200 )  Phone varchar ( 12 ) ) go CREATE TABLE Customer ( ID int PRIMARY KEY  AccountManager varchar ( 100 ) ) go ALTER TABLE Customer ADD FOREIGN KEY ( ID ) REFERENCES Organization ( ID ) ON DELETE CASCADE ON UPDATE CASCADE go One to Many Relationship : Define two tables (example A and B) with their own primary key Define a column in Table A as having a Foreign key relationship based on the primary key of Table B This means that Table A can have one or more records relating to a single record in Table B. If you already have the tables in place use the ALTER TABLE statement to create the foreign key constraint: ALTER TABLE A ADD CONSTRAINT FOREIGN KEY fk_b ( b_id ) references b(id) fk_b : Name of the foreign key constraint must be unique to the database b_id : Name of column in Table A you are creating the foreign key relationship on b : Name of table in this case b id : Name of column in Table B This is a simple example of a classic Order example. Each Customer can have multiple Order s and each Order can consist of multiple OrderLine s. You create a relation by adding a foreign key column. Each Order record has a CustomerID in it that points to the ID of the Customer. Similarly each OrderLine has an OrderID value. This is how the database diagram looks: In this diagram there are actual foreign key constraints . They are optional but they ensure integrity of your data. Also they make the structure of your database clearer to anyone using it. I assume you know how to create the tables themselves. Then you just need to define the relationships between them. You can of course define constraints in T-SQL (as posted by several people) but they're also easily added using the designer. Using SQL Management Studio you can right-click the Order table click Design (I think it may be called Edit under 2005). Then anywhere in the window that opens right-click and select Relationships . You will get another dialog on the right there should be a grid view. One of the first lines reads  Tables and Columns Specification . Click that line then click again on the little [...] button that appears on the right. You will get this dialog: The Order table should already be selected on the right. Select the Customer table on the left dropdown. Then in the left grid select the ID column. In the right grid select the CustomerID column. Close the dialog and the next. Press Ctrl+S to save. Having this constraint will ensure that no Order records can exist without an accompanying Customer record. To effectively query a database like this you might want to read up on JOINs . Create Table Master ( MasterID int identity ( 1  1 ) primary key  Stuff varchar ( 10 ) ) GO Create Table Detail ( DetailID int identity ( 1  1 ) primary key  MasterID int references Master  --use 'references' Stuff varchar ( 10 )) GO Insert into Master values ( 'value' ) --(1 row(s) affected) GO Insert into Detail values ( 1  'Value1' ) -- Works --(1 row(s) affected) insert into Detail values ( 2  'Value2' ) -- Fails --Msg 547 Level 16 State 0 Line 2 --The INSERT statement conflicted with the FOREIGN KEY constraint FK__Detail__MasterID__0C70CFB4. --The conflict occurred in database Play table dbo.Master column 'MasterID'. --The statement has been terminated.