Create Table with PK and FK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Create Table with PK and FK

Author

Topic

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 11:38:19

I am trying to create two tables (CoreBusiness and Addresses) respectively.
The two tables are joined by "ID"(CoreBusiness) and "CB_ID"(Addresses)
With teh 2nd Table Addresses also having an ID field set to autoNumber
I am trying to make the tables and getting errors that

I assuem that I am settign my Primary Keys and Foreign Keys incorrectly...Can anyone out there help me to get this right to create the two tables....

Many to One from CoreBusiness(ID which is autoNumber) to Addresses(CB_ID which also has ID autoNumber field)

Error:
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_Person' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

CoreBusines

CREATE TABLE CoreBusiness2
(
ID INT NOT NULL IDENTITY(1,1),
BldgSqFt FLOAT NULL ,
CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (ID ASC)
ON [PRIMARY]
)
GO

Addresses

CREATE TABLE Addresses2
(
ID INT NOT NULL IDENTITY(1,1),
BusinessMgr VARCHAR(255) NULL ,
CB_ID INT NOT NULL ,
CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (CB_ID ASC)
ON [PRIMARY]
)
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-12-08 : 11:48:36

Every object needs to have a unique name. Even primary keys.

So you can't name it PK_Person on both tables.

How 'bout PK_CoreBusiness and PK_Addresses instead?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-08 : 11:49:36

constraint names have to be unique. You can't have PK_Person on both tables.
I usually do pk_<TableName>.

Note: you also have problems if you rename a table and create another in its place - but you can use sp_rename to rename the constraint when you rename the table.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 11:51:21

Thanks both of you...BUT how about Foriegn Keys....have a hard time understanding that...hwo do I set up the Foreign Key in the Addresses table?

Thanks again for the help

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 11:53:15

I think I would have
CoreBusiness PK = ID
Addresses PK = ID , FK = CB_ID

Where the PK and FK would be linking the two tables? THis value would yield the Many to One relationship

I guess the question is how to create that realtionship while creating the tables? Is that correct?
If not how do I do that?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-08 : 12:11:44

something like
alter table Addresses2 add constraint FK_Addresses2_01 foreign key (CB_ID) references CoreBusiness2 (ID)

Or you can add it into the table create as
constraint FK_Addresses2_01 foreign key (CB_ID) references CoreBusiness2 (ID)

or

CB_ID INT NOT NULL references CoreBusiness2 (ID)

I would go for the alter statement (and for the PK).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 12:16:11

Whats the 01 in this line FK_Addresses2_01

THanks.....

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-08 : 12:30:03

A sequence number to nake the name unique.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 12:34:21

Gotcha....

I did this and the CoreBusiness table created fine

CREATE TABLE CoreBusiness2
(
ID INT NOT NULL IDENTITY(1,1),
BldgSqFt FLOAT NULL ,
CONSTRAINT PK_CoreBusiness2 PRIMARY KEY CLUSTERED (ID ASC)
ON [PRIMARY]
)
GO

I did this with the Adderssess table creation and I get an eror:
Error:
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'CONSTRAINT'.

CREATE TABLE Addresses2
(
ID INT NOT NULL IDENTITY(1,1),
BusinessMgr VARCHAR(255) NULL ,
CB_ID INT NOT NULL ,
CONSTRAINT PK_Addresses2 PRIMARY KEY CLUSTERED (ID ASC)
CONSTRAINT FK_Addresses2_01 FOREIGN KEY(CB_ID) references CoreBusiness2 (ID)
ON [PRIMARY]
)
GO

THANK YOU VERY MUCH FOR YOUR HELP....

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 13:26:52

Any thoughts with the syntax error on my last post...when creating the PK and FK?

Thanks

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 13:47:54

GOT IT..

was missing the , (comma) after:
CONSTRAINT PK_Addresses2 PRIMARY KEY CLUSTERED (ID ASC)

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 13:56:33

DONT KNOW IF I SHOULD POST IN ANOTHER ENTRY? BUT THIS SORT OF IS RELATING TO THE SAME ISSUE..

I am now trying to INSERT into this newly created tables and getting errors:
The INSERT into the CoreBusiness worked fine
I am getting errors when tryign to copy records into the Addresses table...think cause of the FK and PK?????

Addresses Table SQL statement INSERT INTO

INSERT INTO Addresses2(BusinessMgr, CB_ID)
SELECT [Business Mgr], CB_ID
FROM Addresses

ERROR: NOTING THAT ID was the PK in BOTH TABLES
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Addresses2_01".
The conflict occurred in database "BusinessDatabaseTestSilverlight", table "dbo.CoreBusiness2", column 'ID'.
The statement has been terminated.

Do I have to copy records with the INSERT INTO and then use ALTER to add the PK and FK?

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-08 : 14:18:04

you're inserting a value doe that not exist in CoreBusiness2

INSERT INTO Addresses2(BusinessMgr, CB_ID)
SELECT [Business Mgr], CB_ID
FROM Addresses o
WHERE EXISTS (SELECT * FROM CoreBusiness2 i WHERE i.ID = o.ID)

Or to find the ones that are not in CoreBusiness2

SELECT *
FROM Addresses o
WHERE NOT EXISTS (SELECT * FROM CoreBusiness2 i WHERE i.ID = o.ID)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 15:12:04

INteresting results...
In CoreBusiness table I have 1887 records
In Addresses I have 1925 records
So you can see there is a many to one relationship going on...This is only test data...

I think I should be using WHERE i.ID = o.CB_ID instead of WHERE i.ID = o.ID because the PK - FK relation is ID - CB_ID from CoreBusiness to Addresses
I am positive that there is an ID - CB_ID match for every record...only there are multiple CB_ID matching with single ID

If I do this I get 1365 Records Copied in...I know there are 1925 in teh Addresses table adn thats all the records are linked

INSERT INTO Addresses2(BusinessMgr,CB_ID)
SELECT [Business Mgr],CB_ID
FROM Addresses o
WHERE EXISTS (SELECT * FROM CoreBusiness2 i WHERE i.ID = o.ID)

If I do this I get 1405 Records Copied in...I used CB_ID because CB_ID is the foreign Key to ID in the CoreBusiness Table

INSERT INTO Addresses2(BusinessMgr,CB_ID)
SELECT [Business Mgr],CB_ID
FROM Addresses o
WHERE EXISTS (SELECT * FROM CoreBusiness2 i WHERE i.ID = o.CB_ID)

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 15:17:53

Wait a second...when I recreate the CoreBusiness table I am recreating the ID field...so I think I am breaking the Relationship from ID to CB_ID
hmmmmm

Jaykappy
Starting Member

19 Posts

Posted - 2011-12-08 : 17:21:46

Yea that was it...I think I got what I needed from this...my major problem is that I have two tables existing...code is written in Silverlight and I have to now modify the tables and create AutoNumbers and a new FK..
If I recreate a new Parent table with AutoNumber it breaks the relationship.
I moved the unique value to another field and then recreated the table, thus retaining my connection to the child table.
I then updated the FK in teh child table with the newly created Unique ID from the Parent table and recreated the PK and FK on the child table...
But when I try and replace this in silverlight the sky falls apart....So as it stands I got the talbes in SQL server working...
anyone have any suggestions on how to slip these into Silverlight without error please let me know...
THanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources