34,587
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE project (id INT IDENTITY(1,1),projectname VARCHAR(10) UNIQUE )
CREATE TABLE part(id INT IDENTITY(1,1), partno VARCHAR(10) UNIQUE)
CREATE TABLE bom(id INT IDENTITY(1,1),
projname VARCHAR(10) FOREIGN KEY REFERENCES project(projectname),
partno VARCHAR(10) FOREIGN KEY REFERENCES part(partno))
INSERT project(projectname) SELECT 'project1' UNION ALL SELECT 'project2'
INSERT part(partno) SELECT '4012033-1' UNION ALL SELECT '4012033-2'
SELECT * FROM project
/***********************************
id projectname
----------- -----------
1 project1
2 project2
***********************************/
SELECT * FROM part
/***********************************
id partno
----------- ----------
1 4012033-1
2 4012033-2
***********************************/
INSERT INTO bom(projname,partno)
SELECT 'project1','4012033-1'
UNION ALL SELECT 'project1','4012033-2'
UNION ALL SELECT 'project2','4012033-1'
SELECT * FROM bom
/***********************************
id projname partno
----------- ---------- ----------
3 project1 4012033-1
4 project1 4012033-2
5 project2 4012033-1
(3 行受影响)
***********************************/