sql server – 2008 – creating primary key, foreign key and default constraint _ journey to sql...

11/3/2014 SQL SERVER 2008 Creating Primary Key, Foreign Key and Default Constraint | Journey to SQL Authority with Pinal Dave

http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/ 1/32

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

SEPTEMBER 8, 2008 BY PINAL DAVE

SQL SERVER 2008 Creating Primary Key,Foreign Key and Default Constraint

Primary key, Foreign Key and Default constraint are the 3 main constraints that need to be

considered while creating tables or even after that. It seems very easy to apply these constraints but stillwe have some confusions and problems while implementing it. So I tried to write about these

constraints that can be created or added at different levels and in different ways or methods.

Primary Key Constraint: Primary Keys constraints prevents duplicate values for columns and

provides unique identifier to each column, as well it creates clustered index on the columns.

1) Create Table Statement to create Primary Key

a. Column Level

USE AdventureWorks2008

GO

CREATE TABLE Products

(

ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,

ProductName VARCHAR(25)

);

GO

b. Table Level





(

ProductID INT,

ProductName VARCHAR(25)

CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

);

GO

2) Alter Table Statement to create Primary Key

ALTER TABLE Products

ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

GO

3) Alter Statement to Drop Primary key

ALTER TABLE Products

DROP CONSTRAINT pk_products_pid;

GO



Foreign Key Constraint: When a FOREIGN KEY constraint is added to an existing column orcolumns in the table SQL Server, by default checks the existing data in the columns to ensure that allvalues, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.

1) Create Table Statement to create Foreign Key

a. Column Level


GO

CREATE TABLE ProductSales

(

SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,

ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES



Products(ProductID),

SalesPerson VARCHAR(25)

);

GO

b. Table Level

CREATE TABLE ProductSales

(

SalesID INT,

ProductID INT,

SalesPerson VARCHAR(25)

CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),

CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES

Products(ProductID)

);

GO

1) Alter Table Statement to create Foreign Key

ALTER TABLE ProductSales

ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES

Products(ProductID)

GO

2) Alter Table Statement to Drop Foreign Key

ALTER TABLE ProductSales

DROP CONSTRAINT fk_productSales_pid;

GO



Default Constraint: Default constraint when created on some column will have the default datawhich is given in the constraint when no records or data is inserted in that column.

1) Create Table Statement to create Default Constraint

a. Column Level


GO

CREATE TABLE Customer

(

CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY,

CustomerName VARCHAR(30),

CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN'

);

GO

b. Table Level : Not applicable for Default Constraint

2) Alter Table Statement to Add Default Constraint

ALTER TABLE Customer

ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress

AGO



3) Alter Table to Drop Default Constraint

ALTER TABLE Customer

DROP CONSTRAINT df_customer_Add

GO

Reference : Pinal Dave (http://blog.SQLAuthority.com (http://blog.SQLAuthority.com))

This entry was posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query,

SQL Scripts, SQL Server, SQL Tips and Tricks, SQLAuthority, T SQL, Technology. Bookmark thepermalink.

About these ads (http://en.wordpress.com/about-these-ads/)



120 thoughts on SQL SERVER 2008 CreatingPrimary Key, Foreign Key and Default Constraint

Howie | September 18, 2008 at 2:13 pm

i see the different between column level & table level in terms of creating them, but whats thesignificance by having a foreign key defined at column level ?

Reply

smita | September 23, 2008 at 4:05 pmHi! Pinal ,

Could you please tell me, what is difference between defining the constraint on column level orTable level?

Reply

smita | September 26, 2008 at 4:43 pm

Hi!How can we add Unique constraint through Design mode? I do not want Code or query.

Reply

Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authoritywith Pinal Dave

Edwin | December 9, 2009 at 3:59 amSir,

I am newbieenot clear on CREATE TABLE.

CREATE TABLE dbo.Project (

ProjectID VARCHAR(10) NOT NULL,ProjectName VARCHAR (50) NOT NULL,ProjectDesc VARCHAR (50) SPARSE NULLCONSTRAINT pk_project_pid PRIMARY KEY (ProjectID);

GO



ALTER TABLE dbo.ProjectDROP CONSTRAINT pk_project_pid;

GO

ALTER TABLE dbo.ProjectADD CONSTRAINT pk_project_pid PRIMARY KEY (ProjectID);

go

Questions:1. Its necessary to DROP CONTRAINT pk_project_pid?

2. Its a good practice to create constraint in Table level?

Many thanks for your kind help.Edwin

Reply

satish | September 22, 2009 at 6:54 pmhi..,

can u notify d difference between primaryn foreign key constraints cn anyone xplain me in detailtanx fr ur kind response.

Reply

Avinash Reddy Munnangi | May 19, 2013 at 12:02 pmhi satish,

suppose we have two tables like employee and department emptable have few columns likeeno,ename,sal,deptno and same like dept table is also having same columns like deptno,dnameso we want establish the relation between emp table and dept table there is a need to create

primary and foreign key relationship on these two tables.make sure that these two tables samethe same columns. in my example I have taken one deptno column in emp table and samedeptno in dept table also then only this relation is established.

only one primary key is allowed per table and if we created any primary on any particular thatthat column accepts only unique values and it should not accept null values and it automaticallycreates clustered index on that column .

these constraints are very valuable in any database without these there is a chance to misleadthe data

Reply

Diane | December 10, 2009 at 7:50 am

My primary key table, PrimaryLiterals stores two different types of literals race and ethnic. In



My primary key table, PrimaryLiterals stores two different types of literals race and ethnic. In

another table, I have two different columns that refer back to the key, PrimaryLiteralID, of primarykey table RaceLiteralID and EthnicLiteralID. Is it possible to create two foreign key constraintsboth referencing the PrimaryLiteralID or do two separate tables need to be created?

Reply

Pinal Dave | December 11, 2009 at 1:25 am

Hello Diane,

A primary key can be referenced by multiple foreign keys.

Kind Regards,Pinal Dave

Reply

janvi | April 30, 2010 at 2:54 pm

hi,i m using sql server 2008 and for that i have one table named tabdealermaster in that i m usingone primary key as DealerId .and in same table i m using that field as foreign key asdealer_ParentId .is it possible? m i right?

plz tell me. its urgent

Reply

viplav bhushan | April 12, 2012 at 3:50 pmyes , it is.but not in all cases

Reply

fahad | January 13, 2010 at 6:26 pmjust want to thanks you for your priceless work

Reply

hamzah | February 4, 2010 at 6:19 pmhow can i use default primary key such as (0 ,1,2,3,4,5,6 )

for the data

Reply

ashwini | February 17, 2010 at 11:15 am



how to add foreign key when we have more than two tables

Reply

Pinal Dave | February 17, 2010 at 6:38 pmHello Ashwini,

Your question is not clear to me. I think you want to refer a primary key in multiple tables. You cando that as a primary key can be referenced from multiple tables.

Regards,

Pinal Dave

Reply

ak | May 14, 2010 at 11:47 pmThis is the good question.i will give u answr latter.

Reply

Prathyusha | March 25, 2010 at 4:13 amHello sir,im creating a new Foreign key in Factresellersales tableusing PK in Dimtime table and timekey(PK)

the below statement gives an error I cannot understand the reason why? please guide me the correctprocedureIm new to SQL server

alter table dbo.FactResellerSalesADD constraint fk_Timekeyforeign key (TimeKey) References DimTime(TimeKey)

Go

the error I get is ,Foreign key fk_Timekey references invalid column TimeKey in referencing table

FactResellerSales.

Reply

Brian Tkatch | March 25, 2010 at 6:31 pm@Prathyusha

Make sure DimTime.TimeKey is a valid COLUMN, has a UNIQUE INDEX, andFactResellerSales.TimeKey is the same data type.



Reply

Pinal Dave | March 25, 2010 at 9:11 pmHello Prathyusha,

Even the syntax of your statment is correct but following statement should also complete:

alter table dbo.FactResellerSalesADD FOREIGN KEY (TimeKey) References DimTime(TimeKey)Go

Let us know if you still get the error.

Regards,

Pinal Dave

Reply

kk | December 13, 2012 at 10:45 amThank for ur Wonderful Support

Reply

Mazhar | April 24, 2010 at 11:45 pmUr posts r really helpful..thanx..

Reply

Nripin | June 2, 2010 at 12:43 pm

Great Article thanks !!

Reply

Yogesh Bombe | June 15, 2010 at 4:41 pmSir,

Sir i am firstly going to make table using normal forms so please you have help me to create tablewith discretion.{custid,firstname,middlename,Lastname,birthofdate,accountcreationdate,Address,type(creadit orcash),mobilenumber(multivalue),emailid(multivalue).

Reply

Nagalatha | June 22, 2010 at 10:14 am



Sir,I want how to learn code in c# .net,please specify if there is any book,site

Reply

Alok | August 26, 2010 at 11:30 amSir,

What is the difference between table level and column level constraints?

Actually, when you create a column level constraint and generate the script of the same table, itshows as table level.

Thanks in Advance

Reply

kishsore kumar.g | August 28, 2010 at 12:15 pmok i have understand

Reply

Preeti | August 31, 2010 at 3:08 pmSir,is it possable to create a foreign key on unique key colum?i have table which has a unique key (organization_code,product_barcode) and i want to add aforeign key from another table which has these two column s in this table please help me

Preeti

Reply

chathuri | September 5, 2010 at 5:45 pmCREATE TABLE Software_Manuals (item_no varchar(40)PRIMARY KEY,

title varchar(100),descript varchar(100),vers varchar(30),manufacturer varchar(50));

SELECT *

From Software_Manuals

INSERT INTO Software_ManualsVALUES (100,User guide,'Adout Hardware,2009,Samsung);



INSERT INTO Software_ManualsVALUES (101,Software testing,'About Software,2008,Samsung);

INSERT INTO Software_ManualsVALUES (102,Protected mode,'Security,2010,Kaspaskey);

INSERT INTO Software_ManualsVALUES (103,Macintosh,'MAC address,2007,LG);

INSERT INTO Software_ManualsVALUES (104,Educational software,'About Education,2011,Joeant);

CREATE TABLE Copy_SW_Manual (access_no integer PRIMARY KEY,item_no varchar(40)REFERENCES Software_Manuals);

SELECT *

FROM Copy_SW_Manual

INSERT INTO Copy_SW_ManualVALUES(100,0001);




INSERT INTO Copy_SW_Manual

VALUES(104,0005);

CREATE VIEW VW_SW_MANUAL (title,manufacturer,vers,access_no)ASSELECT SM.title,SM.manufacturer,SM.vers,CSM.access_noFROM Software_Manuals SM, Copy_SW_Manual CSMWHERE SM.item_no=CSM.item_no

Sir,this is argent. i cant understand these questions.

1) if the inserted row contains an access that exists in the table, then update the version, title &manufacture columns of Software_manuals table with the inserted data

2) Else,if the title,manufacture and version exists in the Software-Manuals table,insert a new row to



2) Else,if the title,manufacture and version exists in the Software-Manuals table,insert a new row toCopy_SW_Manual table with its item information referncing the existing row of the

Software_Manuals table

3)Else, insert rows to Software_Manuals and Copy_SW_Manual tables to reflect the newinformation

Please send me the Answers of these questions pls..sir

Thankyou..!

Reply

Avinash Reddy Munnangi | May 19, 2013 at 12:23 pmhi chaturi,

you didnt mention your doubt clearly but you created a view using both of those table I will tell

you something about view.

a view is nothing but a query or output of a query suppose we have to retrieve a data usingquery we can get the data successfully but again you want that data so you have to write thesame query again n again so to over come these type of problems a views coming into picture Imean you can create view within that view you write your required query view are stored indatabase. so we want to retrieve the data again no need to write a query simply call a view with

select statement,some important about view is view cant store any data just its stores the query.

we want to retrieve the data again you can run view in back ground the query is going to theoriginal table and retrives our required data .view with in a view is also possible .

Thanks,Avinash Reddy Munnangi

Reply

stephen | October 20, 2010 at 11:06 amIve a problem in sql server just could you tel me ?there are four tables in my database and there are two primary keys in two different table I need tofilter data s by using those primary keys when I prepare reports, but in the beginning Ive given

only 1 primary key as foreign key to other tables

My question is can I insert in this stage the other particular primary key as foreign key to othertables?

its very argent ! I know you can help me

Reply



Pinal Dave | October 21, 2010 at 4:19 pmHello Mr. Stephen,

You can insert the other primary key as foreign key to other tables provided you have the data inthe tables that do not conflict with the combination keys (existing primary key and the new foreignkey). In that case, you will have to make the corrections in the data already existing in the table andthen insert the keys.

Also, after making changes in the design of the table, do not forget to refresh the data in reports.

Kind Regards,

Pinal Dave

Reply

vikas parmar | October 28, 2010 at 11:58 amsir

please tell me how can i create table in sql server.what process of start sql and then reached the tablesheet.and also create contrant primary key foreign key .

Reply

madhivanan | October 28, 2010 at 1:23 pmRead about CREATE TABLE in SQL Server help file

Reply

ram | August 5, 2011 at 4:48 pmfirst you have to create one table then .> go to stored procedure >and write code like a

create procedure [procedure_name](declare variables[while u declare a variable start with ' @ ' and end with ,]example@name nvarchar(50)

Reply

Anbu | November 15, 2010 at 6:52 pmHello Mr.Pinal Dave,



I read your post regarding Constraints in Sql.Its very useful & Easy to understand.

As well,Short and Sweet.

Thanks a lot..

RegardsAnbu with Love

Reply

Krishna | December 5, 2010 at 8:17 amI am a newbee to SQL and I am working on a metrics table. I have the SQL 2008 Express on mycomputer.

The name of the database is PlantMetrics and the name of the table is dbo.PerformanceIndexes.

I have three columns containing:

PlantNameMonthYear

The combination of values in these three columns is unique. When I post the metrics for each plantfor each month, the values of PlantName, Month and Year will repeat from record to record.

Can I denote these three columns as primary keys? If so how?

Or do I leave the table without primary key constraint?

I want to add other tables to the database with details of other characteristics for the plants andrelate them to this table.The other tables will have unique values for the PlantName etc.

Reply

Kawshi | December 11, 2010 at 12:33 pmThanx for your Example Posts.They are really really helpful

Reply

vinoth | December 16, 2010 at 5:46 pmhi sir,

i am vinoth we are using sql server 2008 i want to display the content table for daily updates.

Reply



vinoth | December 16, 2010 at 6:01 pm

hi sir,

i am vinoth. we are using for sql server 2008. i want display

recorded data for example when i entered in office using

my id card that time..the product software recorded

already recorded and new record data i want to display

screen with using sql server 2008.

which command we have to use. please give me solution..

Thanks & Regards,vinoth.A

Reply

madhivanan | December 17, 2010 at 8:51 pmIf you have datetime column with default getdate(), you can find it

Reply

manideep | December 27, 2010 at 2:24 pmcan you please tell me about logins and users n sql server?

Reply

Anshu Verma | January 19, 2011 at 4:17 pmHi Pinal Sir,Thanks for your tutorial about adding primary key and foreign key using alter statement atcolumn level and table level.Anshu

Reply

Anshu Verma | January 19, 2011 at 4:31 pmHi Pinal Sir,In the scenario of foreign key and primary key both are providing the same feature but what is theactual difference between column level and table level ?

Reply



suresh | February 16, 2011 at 6:40 pmhi i want create a login form in master page and relavented tables in database

Reply

Dee | February 21, 2011 at 2:44 amCREATE table Customers(CustomersID char(5) not null,Address varchar (60) null,City char(15) null,Phone char (24)null,

Fax char (24) null,constraint pk_Customers primary key (CustomerID))

CREATE table Orders(OrdersID int,CustomerID char not null,

Orderdate datetime null,Shippeddate datetime null,Quantity int null,constraint pk_Orders primary key (OrdersID, CustomerID))

Reply

jogender jatt | April 17, 2013 at 12:16 pmi am now satisfy with your answer

thanks

Reply

Nidhi Dave | March 17, 2011 at 10:52 amHelloPinal

Your posts are really very helpful

thank you

Reply

ramakrishna | April 9, 2011 at 8:45 am



Hi PinalI create salespeople tablecreate table salespeople(snum int CONSTRAINT pk_snum PRIMARY KEY,sname varchar(50),cityvarchar(50),comm decimal(12))i inserted data like this

insert into salespeople values(1002,bama,'bangalore,.18)insert into salespeople values(1003,kama,'Bobbili,.15)insert into salespeople values(1004,anjuri,'vizag,.14)-then i create customer tablecreate table customer(cnum int INT CONSTRAINT pk_productSales_sid PRIMARY KEY,Cname varchar(50),

city varchar(50),rating int ,snum int CONSTRAINT fk_snum_pid FOREIGN KEY REFERENCES salespeople(snum))inser valuesinsert into customer values(200,jayanth,'bangalore,100,1004)insert into customer values(2005,bhagyasri,'guntur,200,1006)-i create order table

create table orders(onum int CONSTRAINT pk_productSales_sid PRIMARY KEY,amount decimal(10),Orderdate datetime,snum int CONSTRAINT fk_snum2_pid FOREIGN KEY REFERENCES salespeople(snum),cnum int CONSTRAINT fk_cnum_pid FOREIGN KEY REFERENCES customer(cnum))

i got the errorMsg 2714, Level 16, State 4, Line 1

There is already an object named fk_snum_pid in the database.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.

How to solve those errorsplz send details urgent pinla

Reply

sravanthi | April 9, 2011 at 10:54 amHi Pinal Sir,I hava a problem in creating foreign key in my sql table.im using sql 2008.Can i set two foreign keys in two child tables with references to one unique key in main table .

pleas help me.

Reply

ram | August 5, 2011 at 4:34 pm

try to chage your main table unique key bcoz unique key access from the null values so try to



try to chage your main table unique key bcoz unique key access from the null values so try touse primary key then u il set the foreign key ur table

Reply

manoj bhatt | May 12, 2011 at 11:51 pmi am now satisfy with ur answer

thanks

Reply

manoj bhatt | May 19, 2011 at 1:21 amdear sir,i have a table called student_ id with 3 column id(pkey),name and course.

i wants to access id (start with 3 and end with 8,means 5 values)

hw can this is possible with asp.net application

Reply

ram | August 5, 2011 at 4:28 pmcan anyone help me!!!when ever i create identity primary key tat error showing all the times

Must declare the scalar variable @ID

Reply

Dinesh | August 26, 2011 at 4:10 pmHAI,

I want learn SQL more.Is any way to understand SQL easely.REGARDSDinesh

Reply

rama chandra sahu | August 29, 2011 at 3:38 pmCant we create a default constraint at table level ???

Reply

Madhu | September 7, 2011 at 3:12 pmHi Mr. Pinal,



One table having one cluster Index in the case of one primary key. If more than one primary keysare there in a table, then how many clustered Indexes are created for table?

Please reply me

Thanks & RegardsMadhu.

Reply

madhivanan | September 7, 2011 at 7:16 pmNote that there cannot be more than one primary key in a table

Reply

Madhu | September 8, 2011 at 9:25 am

Hi madhivanan,

we can create more than one primary key we can call it as composite key..

Reply

madhivanan | September 9, 2011 at 4:29 pm

It is a single primary key combined on many columns. You cannot create primary key on eachcolumn seperately.

Reply

yosepkurniawijaya | September 9, 2011 at 9:07 pmHi, greeting from Indonesia. Ive been looking for script of creating a foreign key related to the

primary key from the column level and your explanation was great. Thank you very much! Godbless

Reply

Richardm | September 16, 2011 at 12:56 am

Greetings Pinal Always find your answers helpful and spot on. At the moment Im stuck inSQL/Server hell and cant find a solution. I have a table that I cant drop, cant truncate, cant doanything with because of this message it is being used for foreign key constraint enforcement. Ihave dropped ALL of the constraints on the table, and still cant get around this error. There is aunique index that is being referenced that cant be dropped either because of the same errorresponse. Im stuck in a circular hell. How do I get out of hell with this table?

Reply



Pankaj | September 28, 2011 at 4:01 pm

Your posts are really usefull

Reply

Tha | October 7, 2011 at 8:59 amThanks for your post.

Reply

Brent | December 6, 2011 at 8:28 pmThank you, always seem to forget the exact syntax on many of those.

Reply

dharmesh | December 9, 2011 at 10:10 amhow to insert a manual int value in primary key , sql server 2008

Reply

madhivanan | December 15, 2011 at 8:06 pmYou can do it in INSERT and UPDATE statements

Reply

Avinash Patil | December 12, 2011 at 12:26 pm

Hi sir,My question is-In sql server authentication mode which is more secure authentication? (Windows or Sql server)

Reply

vipin kumar | January 17, 2012 at 1:10 pm

window authentication mode used when you use your application with your system and sqlserver authentication is used when multiple application used a centrally database .

ReplyPingback: SQL SERVER Various Ways to Create Constraints Quiz Puzzle 17 of 31 SQLServer Journey with SQL Authority



Sharan Chakradhar | February 3, 2012 at 7:37 pmHow do I fetch MAC address in SQL server to implement in triggers.

Reply

Julio Cesar | March 23, 2012 at 10:22 pmDave, thanks for you feedback, because this examples are very good and help me so much.

Thanks,

JCSL.

Reply

Mohan | April 6, 2012 at 10:50 amGreat sir, thanks

Reply

kurt | April 18, 2012 at 3:41 pm

How to generate a default Primary KeyBackground: There is a Column PropertiesIdentity Specification option with Identy Incrementand Identity Seed in sqlsrv.

Q: Is it possible to assign a default value for new records like getYear() +get_SQLSRV_Global_Sequence_Number() feature/trigger available?

1) Example:

a) Syntax(SELECT CONVERT( NUMERIC(4), (SELECT CONVERT(VARCHAR(4), GETDATE(), 112) AS[YYYY]))) +



Please refer or provide an sqlsrv example if possiblewe would like to have a standardapproach(dbas, C# developers) regarding primary key assignment

-kurt

Reply

kurt | April 18, 2012 at 3:58 pmForgot to mention thata) external systems (PRODUCERS) post typically + + in a Varchar(50) columnand thatb) im using an additional uniqueidentifier and (newid()) in sqlsrv db for internal integrity.

Reply

Vishal | April 30, 2012 at 7:10 amHello sir,How to create recursive query in SQL Server?

Reply

Nisha | May 9, 2012 at 6:19 pmur post was very nice and useful for me

Reply

Nisha | May 9, 2012 at 6:23 pmHello sir?now i save the data in one table , that values also create a new columns into another table , if anypossible to do automatically create a new columns

Reply

jyothi | May 11, 2012 at 3:03 pmsir,Am Jyothi. Is it posible to create a foreign with out primary and it is a unique key.

Reply

Viji | May 22, 2012 at 5:23 pmHello Sir,Can we drop foreign key which is not a constraint.. How to drop foreign key



Reply

madhivanan | May 23, 2012 at 3:20 pmUse ALTER TABLE and DROP CONSTRAINT statements

Reply

Viji | May 24, 2012 at 1:50 pmThanks for the reply.. I tried with this, its giving error field is not constraint..How to drop that foreign key now

Reply

madhivanan | May 24, 2012 at 3:07 pmYou should find the key name and use it

Drop constraint contrain_name

Reply

Viji | May 24, 2012 at 4:51 pmHow to find the constraint name..

Viji | May 24, 2012 at 4:50 pmHow to find the constraint name

Reply

madhivanan | May 30, 2012 at 2:32 pmexec sp_fkeys table_name

Reply

Viji | June 2, 2012 at 11:48 amThank You Sir.

Reply

chetan | October 1, 2012 at 3:24 pmsp_helpconstraint table_name



Reply

Sapna | May 29, 2012 at 3:52 pmHello,If i run sequence i am getting error Unknown object type SEQUENCE used in a CREATE,DROP, or ALTER statement. how to solve this problem

Reply

madhivanan | May 30, 2012 at 2:44 pmNote that Sequence is available from version 2012 onwards only

Reply

Viji | June 2, 2012 at 12:05 pmHello,How to store URL in tableI want to store URL in my tables in 2008, I will be extracting the same data for report inexcel, if i click on the URL it should take me to particular site, I hope to get solution for this..Thank You in advance..

Reply

Ramesh | June 7, 2012 at 8:02 pmHi,

I am Ramesh from Chennai, how many tables allowed in sql server 2008.

Reply

madhivanan | June 18, 2012 at 4:37 pmIt depends on the harddisk volume. Read about Maximum capacity specifications in SQLServer help file.

Reply

Venkat Kilari | June 13, 2012 at 3:40 pmDatabase objects include objects such as tables, views, stored procedures, user-defined functions,

triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannotexceed 2,147,483,647.

Reply



Bob Wood | July 12, 2012 at 8:50 pmAwesome help as always. Why I dont come here first every time, I have no idea.

Reply

mit | July 17, 2012 at 10:55 pmpinal sir you r the genius hats off to your site really!

Reply

mit | July 17, 2012 at 10:56 pmactually i had problem with default with alter you just solved it thanks sir again i say yu the best inthe world for sql

Reply

Karthick | July 26, 2012 at 3:07 pmHi Sir.

How to add the default value for exsiting column

Reply

Arooj | July 26, 2012 at 11:53 pmhi sir,I need to copy the value of a primary key into another column ofanother table which is a foreign key how will i do it in sql server 2008 r2

Reply

kalia | August 14, 2012 at 3:25 pmawsm

Reply

Satyabrata Panda | August 24, 2012 at 11:21 amHi sir ,There are two table like User and comment and columns like(Userid,Conversation,click abuse ,commentid and category) for user table and (commentid,Comments,userid).when i insert some data through asp.net website i want to update these data oftwo tables in database.plz what is the code for sql database



Reply

sharath | September 12, 2012 at 10:06 am

can we give reference of foreign key to primary key of table which is present in other schema table

Reply

shiva | September 21, 2012 at 12:36 pmHey

can any one guide me on !!

How to Drop bunch of tables from database ??

Reply

Sushil Chhimpa | October 6, 2012 at 9:10 am

drop table tablename

Reply

Sushil Chhimpa | October 6, 2012 at 9:55 amHow to use triggers

Reply

Shobana | November 7, 2012 at 12:11 pmHI I have a table test with primary and foreign key.and i created view with that table as vwtest.i can insert into the vwtest as it contain single table.how can i get primary,foreign keys into the views vwtest from the main table test

Reply

Pat | November 19, 2012 at 10:26 pmI have what appears to be a painstakingly manual task. I need to find all the foreign keys in a DBhowever they are all logical. There are no constraints in the DB. Can you reference a set ofthechniques to facilitate this?Thank you

Reply

madhivanan | November 20, 2012 at 11:45 amWhat did you mean by logical? If they have foreign key constraints, it is easy to fine them



Reply

jeff | January 7, 2013 at 6:37 pmhello guys, i really need your help, im using sql server 2008 and in creating tables when i putforeign key the insert not accept but when there no foreign key the insert accept directly. what can ido to create that foreign key??there is another way to join tables in report without using foreignkey??? please need your help im a beginner..thx

Reply

manish srivastava | February 14, 2013 at 4:06 pmhey friendstell me how to use a foreign key between 2 tables in a single database when one table

have primary key..??

Reply

VIVEK KUMAR THAKUR | February 26, 2013 at 7:37 pmHiI am making a billling software and i cant understand that how can i make data base for billingmy structure is like thisINVOICE NO,SLNO PRODUCTS PRICE QUANTITY TOTAL1234

5678910GRANDTOTALProblem is that if i sell one products then it working but if i sell 10item at a timei can,t understand how to create table for it please help me

VIVEK KUMAR

Reply

mANOJ | March 8, 2013 at 10:53 amWHAT IS THE DIFFERENCE OF GIVING PRIMARY KEY AND FOREIGN KEY CONSTRAINTAT COLUMN LEVEL AND TABLE LEVEL.WE CAN PROVIDE DIRECTLY AT A TABLELEVEL ITSELF RIGHT



Reply

Homina | March 13, 2013 at 1:03 pmhelo sir,how to insert values in a table that contains a foreign key

Reply

Thuli | May 21, 2013 at 3:44 pmHICAN YOU DEFINE SCHEMAS AND DEFAULT CONSTRAINT

Reply

Guest | July 7, 2013 at 11:57 pmcan constraint check the validity of ddl operation??

Reply

Imran Ullah | July 30, 2013 at 3:24 pmFantastic Post.

Reply

Gaurav | August 12, 2013 at 12:57 pmHi

can anybody help me to know the diffrence between database developer, database programmerand data analyst. which things i have to prepare for these profiles. because i am confused in it.please reply ASAP.

Reply

arashdeepgarg | August 29, 2013 at 12:30 pm

i wanna to know with simple example how to apply foreign key in tables

Reply

parveen | January 29, 2014 at 11:31 amcreate table tb(id int constraint pk primary key,name varchar(20)



)select * from tbinsert into tb values(1,pari)insert into tb values(2,mansi)insert into tb values(3,mansi)

create table mb(id int constraint fk foreign key references tb(id),address varchar(20))

select * from mbinsert into mb values(1,sonipat)insert into mb values(2,panipat)

ReplyPingback: SQL SERVER Weekly Series Memory Lane #046 | Journey to SQL Authority withPinal Dave

vishal | February 17, 2014 at 4:10 pmhi i am using vb6 with sql server 2008. i have table named address given the query below:Create Table address([apartment_name][varchar](50) NULL,[door_number][varchar](50) NULL,[street_name_1][varchar](50) NULL,[street_name_2][varchar](50) NULL,[street_name_3][varchar](50) NULL,[village][varchar](50) NULL,[city][varchar](50) NULL,[state][varchar](50) NULL,

[country][varchar](50) NULL,[row_upd_date][date/time] NULL,[apartment_number][varchar](50) NULL,[agn][int]IDENTITY(1,1) NOT NULL,[status][bit] NULL,[pincode][varchar](50) NULL,[user_id][int] NULL,)now that i have to create a field name supplier_id of type integer which links to tables namedsupplier_contact and Supplier_id and Supplier_name. How make supplier_id as foreign in tablenamed address? Reply please

Reply



Blog at WordPress.com. | Customized Misty Lake Theme.

Follow

Follow Journey to SQL Authority with Pinal Dave

Powered by WordPress.com

sql server – 2008 – creating primary key, foreign key and default constraint _ journey to sql...

Documents