How to Design for this [Resolved]

Posted by Jayakumars under C# on 3/20/2017 | Points: 10 | Views : 638 | Status : [Member] [MVP] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

How to Design proper tables . I know how to design tables but which is need for the perfect table?

for ex:My question is for a table need Primary key, foreign key further which option need for the table

I am asking normally for a table we set primary key and foreign key which those things
additional need for table because in future my data may be large amount so i need fetch data quickly so i need proper design tables so any one guide me

how to create proper tables with constraints,indexing etc give sample for this

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 3/20/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

0	@Jayakumar Sir, Let us say we have two tables (Employee and Department) Let's create the Department table as under CREATE TABLE tblDepartment ( DepartmentID int NOT NULL, DepartmentName varchar(255) NOT NULL, CONSTRAINT PK_Department PRIMARY KEY (DepartmentID) ); Let's create the Employee table as under CREATE TABLE tblEmployee ( EmployeeID int NOT NULL, FirstName varchar(255) NOT NULL, LastName varchar(255), Age int, Salary float, DepartmentID int, PRIMARY KEY (EmployeeID), CONSTRAINT FK_EmpDept FOREIGN KEY (DepartmentID) REFERENCES tblDepartment(DepartmentID) ); In the tblDepartment, DepartmentID is the Primary Key and is also a clustered index . In the tblEmployee, EmployeeID column is the Primary Key and is also a clustered index. DepartmentID is the foreign key here. It is the referential integrity constraint. N.B.~ it will be better if you kindly read the tutorial regarding SQL Server at Techfunda ( http://techfunda.com/howto/sql-server ) Read this (http://techfunda.com/howto/8/ ) section for all the doubts about your question. Kindly let us know if you need some more assistance. -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

@Jayakumar Sir,

Let us say we have two tables (Employee and Department)

Let's create the Department table as under

CREATE TABLE tblDepartment (

    DepartmentID int NOT NULL,

    DepartmentName varchar(255) NOT NULL,

    CONSTRAINT PK_Department PRIMARY KEY (DepartmentID)    

);

Let's create the Employee table as under

CREATE TABLE tblEmployee (

    EmployeeID int NOT NULL,

    FirstName varchar(255) NOT NULL,

    LastName varchar(255),    

    Age int,

    Salary float,

    DepartmentID int,

    PRIMARY KEY (EmployeeID),    

    CONSTRAINT FK_EmpDept FOREIGN KEY (DepartmentID)

    REFERENCES tblDepartment(DepartmentID)

);

In the tblDepartment, DepartmentID is the Primary Key and is also a clustered index .

In the tblEmployee, EmployeeID column is the Primary Key and is also a clustered index. DepartmentID is the foreign key here. It is the referential integrity constraint.

N.B.~ it will be better if you kindly read the tutorial regarding SQL Server at Techfunda ( http://techfunda.com/howto/sql-server )

Read this (http://techfunda.com/howto/8/ ) section for all the doubts about your question.

Kindly let us know if you need some more assistance.

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts