如何使用注册表创建关系其他表？ [英] How can I create relationship others table with registration table?

查看：91
本文介绍了如何使用注册表创建关系其他表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在sql中创建了表并试图通过使用foreign和primery键将它们与注册所有剩余的tabeles连接起来，但是不能正常工作，我试图用注册表的ID连接用户名，它给了我错误，你能帮我联系吗？



表是：



1，注册< br $>


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

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个帖子




2 posts

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，




3,

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，




4,

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



5，




5,

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，其他物品




6, other items

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，收件人




7, recipient

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




8

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，




9,

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，< br $>



10,

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，




11,

CREATE TABLE Comments
(
C_id int constraint C_pk   primary key, 
Username varchar(25), 
comment varchar(max), 
date_time varchar(100)
);

推荐答案

我很抱歉这样说，但创建外键关系是非常基本的知识......错误信息你得到的也很简单。

你的 ID 是一个 INT 而你的用户名是 VARCHAR（20）。创建外键约束时，您要确保一个表中的值与另一个表中的值相对应。如果其中一个表的值甚至无法在另一个表中创建，怎么能保证呢？



除此之外你的问题毫无意义。您想在同一个表中将外键放在 ID 和用户名上吗？

以下是关于外键约束的一些内容： http://technet.microsoft。 com / en-us / library / ms175464（v = sql.105）.aspx [ ^ ]。

我建议您阅读，或者这里没有人可以为您做任何事情。 />
祝你好运，听起来像个好项目！
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!


Sander Rossel提供的笔记非常有用。可能会很快描述：从基础开始！



哇，11桌！

另一个注意事项是：我建议减少表格数量至少为3 :)



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. 注册
  2. ItemType
  3. ItemsCollection











  1. 注册



  1. Registration
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)
);

  • ItemType - 存储

    ItemType = {'Laptop', 'Shoes', 'Book', 'Post', 'Uniform', 'Image', ...}



    等


    etc.

    CREATE TABLE ItemType
(
IttID INT IDENTITY(1,1) PRIMARY KEY,
ItemType VARCHAR(50) 
);

  • ItemCollection - 存储有关所有项目的信息

  • ItemCollection - to store information about all items

    CREATE TABLE ItemsCollection
(
ItcID INT IDENTITY(1,1) PRIMARY KEY,
IttID INT FOREIGN KEY REFERENCES ItemType(IttID)
--other fields 
);







    • 我不知道你想如何在 ItemsCollection 和注册表。并且......创造它有共鸣吗？除非您想存储添加数据的信息。在这种情况下，您需要在每个表中添加 RegID 。



    最终结论：重新思考数据库设计。







    所以...将 RegID 添加到 ItemsCollection 表作为外键，那么你将能够定义哪个项目来自或属于哪个用户等。

    [/ EDIT1]



    [ EDIT2]



    这里你有完整的脚本。区别在于：我在临时表上工作（在变量上 - 类型：表）



    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.



    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]



    Here you've got complete script. The difference is: i worked on temporary tables (on variables - type: table)

    --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





    如您所见，共有7项：4属于我，3属于你:)

    有关系数据库的力量！

    [/ EDIT2]



    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]


    这篇关于如何使用注册表创建关系其他表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    其他开发语言最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆