MS SQL/key

Advertisement


Question
sir, i m using sql server 2008, in sql server i have a table city. in city table columns are city_name, city_code, zone.
there is another table plant. in plant table columns are plant_name, plant_code, city_name, city_code and zone. in city table data like this

city    city_code   zone
chapra   M0066       bihar
patna   M111        bihar
delhi   M551        central  
mumbai   M666         west
now i want to insert data into plant table. when i insert 'chapra' in city of table plant city_code never insert other than 'M0066'. how can i do this. sir plz give me your valuable suggestion, i always thankful to you

Answer
Hi Sanyog

I guess you want to implement referential integrity, where "Plant" table should only hold valid cities which exists in master table "City". If this is correct, then I suggest you break you tables like this and create proper primary key and foreign key constraints.

City
city_name, city_code (PRIMARY KEY), zone

Plant
plant_name, plant_code, city_code (FOREIGN KEY)

So, Plant will hold only valid cities. You can get the data back using proper join

SELECT *
FROM Plant p
INNER JOIN City c ON p.City_Code = c.City_Code

Please have a look here for more information on the concept.

http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms191472(v=sql.105).aspx

Thanks
Mohit

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mohit Nayyar

Expertise

Microsoft SQL Server: T-SQL development (stored procedures, triggers, functions), Database optimization, performance tuning, high availability (Clustering, Log Shipping, Mirroring, Replication), scalability, SQL Server migration (Sybase/Oracle to SQL Server), SSIS/DTS, Data Warehousing (Kimball Methodology), ETL (SSIS), Cube (SSAS), Reporting Services (SSRS), database modeling, database administration, Security implementation and typically anything related to Microsoft SQL Server.

Experience

With over 10 years of experience in Database Administration, Development, Business Intelligence solutions and managing enterprise level database solutions based on Microsoft SQL Server.
Publications
IT Magazine: ASP, SQL

Publications
IT Magazine: ASP, SQL
SQLServerCentral.com

Education/Credentials
MCA (Masters)

Awards and Honors
Brainbench Certified: RDBMS
MCTS: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCITP: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCDBA: SQL Server 2000
MCP: SQL Server 6.5

©2016 About.com. All rights reserved.