Click here to Skip to main content
15,867,879 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I created tables in sql and tried to connect them with registration all remaining tabeles by using foreign and primery key, but does not work as I want and i tried to connect username with ID of the Registration table, it gives me error, Can you help me how to connect each other?

The tables are:

1, registration

SQL
Create Table Registration
(
ID int NOT NULL primary key,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,  

    Username varchar(20), 
    Gender varchar(10),
    Home_Address varchar(100),
    Office_Address varchar(100), 
    City varchar(25), 
    State varchar(25), 
    Zip varchar(25), 
    Contact_No int , 
    Email varchar(25)
); 

2 posts

SQL
CREATE TABLE Post

( p_id int constraint p_pk   primary key, 
Username varchar(25), 
Status varchar(25), 
image nvarchar(max),
date_time varchar(100)
);

3,

SQL
CREATE TABLE Books
(
ISBN nvarchar(200) constraint ISBN_pk   primary key, 
Username varchar(25), 
Book_title varchar(25), 
Authorname varchar(25), 
Publicationdate varchar(25),
Purchasedate varchar(25),
Book_edition varchar(25)
);

4,

SQL
CREATE TABLE images
(I_id int constraint i_pk   primary key, 
Username varchar(25), 
Title varchar(25), 
url varchar(50), 
description varchar(100)
);

5,

SQL
CREATE TABLE laptop
(
L_id int constraint L_pk   primary key, 
Username varchar(25), 
Model varchar(25), 
Speed varchar(25), 
Ram varchar(25), 
HD varchar(50), 
Screen varchar(50)
);

6, other items

SQL
CREATE TABLE other_items
(O_id int constraint O_pk   primary key, 
Username varchar(25), 
Item_title varchar(25), 
Item_type varchar(25), 
Item_description varchar(100), 
Itempicture varchar(100)
);

7, recipient

SQL
CREATE TABLE Recipient
(R_id int constraint R_pk   primary key, 
Firstname varchar(25), 
Lastname varchar(25), 
National_id_no varchar(50),
Address varchar(100),
Contact_no varchar(100)
);

8

SQL
CREATE TABLE Shoesimages
(
s_id int constraint Rss_pk   primary key, 
Username varchar(25), standard varchar(25), 
Gender varchar(25), 
Colour varchar(25), 
Description varchar(100)
);

9,

SQL
CREATE TABLE uniform
(U_id int constraint u_pk   primary key, 
Username varchar(25), 
Standard varchar(25), 
Gender varchar(25), 
Colour varchar(25), 
Description varchar(100)
);

10,

SQL
CREATE TABLE Research_paper
(
Rs_id int constraint Rs_pk   primary key, 
Username varchar(25), 
title varchar(25), 
authorname varchar(25), 
year_of_publish varchar(25),
venu varchar(100)
);

11,

SQL
CREATE TABLE Comments
(
C_id int constraint C_pk   primary key, 
Username varchar(25), 
comment varchar(max), 
date_time varchar(100)
);
Posted
Updated 4-May-14 6:05am
v4
Comments
[no name] 2-May-14 16:23pm    
http://stackoverflow.com/questions/23433086/how-can-i-create-relationship-others-table-with-registration-table

I'm sorry to put it like this, but creating foreign key relations is pretty basic knowledge... The error message you're getting is also pretty straightforward.
Your ID is an INT while your Username is a VARCHAR(20). When creating a foreign key constraint you're making sure that a value in one table corresponds with a value in another table. How can this be guaranteed if one of the tables can have values that cannot even be created in the other table?

Other than that your question makes no sense. You want to put a foreign key on ID and Username from the same table?
Here's some reading up on Foreign Key Constraints: http://technet.microsoft.com/en-us/library/ms175464(v=sql.105).aspx[^].
I suggest you read it, or no one here can do anything for you.
Good luck on your assignment though, sounds like a good project!
 
Share this answer
 
v2
Comments
Maciej Los 2-May-14 17:29pm    
Valuable answer, +5!
I had added some extra information in my solution ;)
Sander Rossel 3-May-14 2:16am    
Thanks. Looked at your answer. Makes sense :-)
Notes provided by Sander Rossel are very useful. It could be shortly described: start with basics!

WOW, 11 tables!
Another note is: I'd suggest to decrease the number of tables at least to 3 :)


  1. Registration
  2. ItemType
  3. ItemsCollection




  1. Registration
    SQL
    Create Table Registration
    (
    RegID int IDENTITY(1,1) primary key,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,  
    Username varchar(50), 
    Gender varchar(10),
    Home_Address varchar(100),
    Office_Address varchar(100), 
    City varchar(25), 
    State varchar(25), 
    Zip varchar(25), 
    Contact_No int , 
    Email varchar(25)
    ); 
  2. ItemType - to store information like
    ItemType = {'Laptop', 'Shoes', 'Book', 'Post', 'Uniform', 'Image', ...}

    etc.
    SQL
    CREATE TABLE ItemType
    (
    IttID INT IDENTITY(1,1) PRIMARY KEY,
    ItemType VARCHAR(50) 
    );
  3. ItemCollection - to store information about all items
    SQL
    CREATE TABLE ItemsCollection
    (
    ItcID INT IDENTITY(1,1) PRIMARY KEY,
    IttID INT FOREIGN KEY REFERENCES ItemType(IttID)
    --other fields 
    );



I have no idea how you want to create relationship between ItemsCollection and Registration tables. And... Is there a reson to create it? Unless you want to store information who add data. In this case, you need to add RegID into each table.

Final conclusion: Re-think the databse design.


[EDIT1]
So... Add RegID into ItemsCollection table as a foreign key, then you'll be able to define which Item comes from or belongs to which user, etc.
[/EDIT1]

[EDIT2]

Here you've got complete script. The difference is: i worked on temporary tables (on variables - type: table)
SQL
--Registration  
DECLARE @Registration TABLE (RegID int IDENTITY(1,1) primary key,
							FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL,  
							Username varchar(50), Gender varchar(10),
							Home_Address varchar(100), Office_Address varchar(100), 
							City varchar(25), [State] varchar(25), Zip varchar(25), 
							Contact_No int , Email varchar(25))
--insert data
INSERT INTO @Registration (FirstName, LastName )
VALUES('Maciej','Los'), ('Member','10651775')

--ItemType
DECLARE @ItemType TABLE (IttID INT IDENTITY(1,1) PRIMARY KEY, ItemType VARCHAR(50));
 --insert data
 INSERT INTO @ItemType (ItemType)
 VALUES ('Laptop'), ('Shoes'), ('Book'), ('Post'), ('Uniform'), ('Image')

--ItemsCollection
DECLARE @ItemsCollection TABLE (ItcID INT IDENTITY(1,1) PRIMARY KEY, IttID INT, RegID INT);
--insert data
INSERT INTO @ItemsCollection (IttID ,RegID )
VALUES(1,1),(2,1),(4,1),(6,1),(1,2),(3,2),(5,2)


--display related data
SELECT IC.ItcID, IC.RegID, RE.FirstName, RE.LastName, IC.IttID, IT.ItemType 
FROM @ItemsCollection AS IC LEFT JOIN @Registration AS RE ON IC.RegID = RE.RegID 
	LEFT JOIN @ItemType AS IT ON IC.IttID = IT.IttID 



Result:
1	1	Maciej	Los		1	Laptop
2	1	Maciej	Los		2	Shoes
3	1	Maciej	Los		4	Post
4	1	Maciej	Los		6	Image
5	2	Member	10651775	1	Laptop
6	2	Member	10651775	3	Book
7	2	Member	10651775	5	Uniform


As you can see there are 7 items: 4 belongs to me and 3 belongs to you :)
There is a power of relational databases!
[/EDIT2]
 
Share this answer
 
v3
Comments
Sander Rossel 3-May-14 2:15am    
Good tips. It's weird to have a table for each possible tradable item. Although the OP does want some additional info with each item. I suggest he uses a text field for this (so the trader can just describe his item as he wishes) or an XML field with a specific format for each pre-defined item type.
But a complete database re-design (and probably re-taking some SQL courses) couldn't hurt in this case.
Have my 5.
Maciej Los 3-May-14 4:02am    
Thank you, Sander ;)
Member 10651775 3-May-14 11:51am    
Thank you, macieej, for your suggestion. the thing i want is all remaining tables to be combine with the registration table because i want it to create user profile for what users donate and collected from donors that might be book, uniform, etc. keeping all things in to his profile. what I think was if I created different table with username and connect with registration table by using primery and foreign key constraint what will create for me userprofile. what would you suggest me?
Maciej Los 3-May-14 12:01pm    
See updated answer (EDIT section).
Member 10651775 3-May-14 12:32pm    
I am trying to create user_ID and connect with registartion. for the Post table will be look like this. do you think this will create profile user?
CREATE TABLE Post
(
p_id int constraint p_pk primary key,
user_id int NOT NULL, --This refers to Registration.ID and matches the type defined there
Status varchar(25),
image nvarchar(max),
date_time varchar(100),
FOREIGN KEY ( user_id) REFERENCES [Registration] ( id ) ON UPDATE NO ACTION ON DELETE CASCADE
);

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900