int NOT NULL references dbo.Party(PartyId),įoreign Key to multiple tables at the same time PartyTypeId as cast(1 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID) PartyTypeId as cast(2 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID) PartyTypeId tinyint references dbo.PartyType(PartyTypeId), Heres a rough example using your posted schema: create table dbo.PartyType Or (my choice) model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity. You could create a default group for every user and have tickets simply owned by either a true Group or a User's default Group. Perhaps in future you will want to allow a single ticket to be owned by multiple users or groups? This design does not enforce that a ticket must be owned by a single entity only. You could create M:M reference tables enabling both ticket:user and ticket:group relationships. You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table. As always, the right design depends on your needs.

You have a few options, all varying in 'correctness' and ease of use. (CustomerEmployee INT NOT NULL PRIMARY KEY /* Same key identifies customer and employee */,įOREIGN KEY (CustomerEmployee) REFERENCES Customer (CustomerNum),įOREIGN KEY (CustomerEmployee) REFERENCES Employee (EmployeeNum)) This does assume that a common key is used to identify both customers and employees. Such a table might have a single foreign key that references both the Customer table and the Employee table. One possible example: a StaffDiscount table identifying customers who are also employees and are eligible for a discount. It is unusual in my experience but it potentially makes sense as a way to implement certain business rules with a meaning similar to 'A must be both B and C'. For more information, see Delete Primary Keys and Create Primary Keys.One foreign key referring to multiple table's primary keys To modify a PRIMARY KEY constraint using Transact-SQL, you must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition. To remove a column from the key, simply remove the column name from the Column name list. Then add the columns back in the order you want. In the Index Columns dialog box, remove the columns from the primary key. Select Columns, and then click the ellipses (.) to the right of the property. For more information about fill factors and their uses, see Specify Fill Factor for an Index. If this option is not selected, a unique nonclustered index is created.Įxpand the Fill Specification category and type an integer from 0 to 100 in the Fill factor box. If this option is not available for your index, you must first clear this setting on the existing clustered index. Only one clustered index can exist per table. To create a clustered index for the primary key, select Create as CLUSTERED, and select the option from the drop-down list box. Make sure that your new name does not duplicate a name in the Selected Primary/Unique Key or Index list. In the Indexes/Keys dialog box, select the primary key index from the Selected Primary/Unique Key or Index list.Ĭomplete an action from the following table: To Open the Table Designer for the table whose primary key you want to modify, right-click in the Table Designer, and choose Indexes/Keys from the shortcut menu. Using SQL Server Management Studio To modify a primary key You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor. You can modify a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL.

Sqlitestudio multiple primary keys