sql script creating table with multiple primary keys
I am trying to execute following sql script in sql-server 2000 query analyzer
CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)
Its give's me following error :-
Cannot specify multiple primary key constraint
Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.
Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?
You can only have one primary key, either make all of the items a compound primary key, or make one a primary key and make the rest indexed with no duplicates which is just like a primary key.
indexed with no duplicates which is just like a primary key means what.
index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.
No Duplicates: To enforce uniqueness on the rows in a table. No two rows in a table can have the same value in a field that is Indexed with the no duplicates restraint.
A Primary Key of a table is not only automatically indexed; but is also restrained by the no duplicates restraint. This is to insure that the primary key always produces a unique record from within the table.
primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.
Last edited by Ron Weller; 02-05-2007 at 04:47 AM.
i have understood many things from this reply.Thanks for that.
you said "A primary key is used to relate a table to foreign keys in other tables."
but what i know is
1)the column(table1) which is referring another column(table2) is called foreign key and the column which is referenced is called primary key.
Exactly! that is what a foreign key is. It is when you store the primary key from one table in another table. This allows a link between the two tables. For example if you store the primary key from the customers table in the orders table then you can link the orders table to the customers table and for each order you can find the customer who placed that order.
how many types of restraints are there like the one you said
1)no duplicates restraint
can u create a table for showing the syntax of
3)no duplicates restraint
By rperez in forum Database
Last Post: 01-02-2009, 05:14 PM
By bubberz in forum ASP.NET
Last Post: 08-23-2005, 03:19 PM
Last Post: 02-12-2003, 10:31 AM
By Chris in forum Database
Last Post: 08-20-2002, 01:14 AM
By Beginner-Brad in forum Database
Last Post: 01-27-2001, 05:13 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL